I am querying the following:
SELECT "date" FROM "database"
and receive the following table:
date
2021-03-08 05:05:31+00
2021-03-08 05:10:31+00
The times displayed are in GMT. How can I convert them to CET, taking into account daylight savings?
Asked
Active
Viewed 308 times
-1
MathMan 99
- 665
- 1
- 7
- 19
-
1[convert_tz](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz) – Andrew Jan 10 '22 at 15:28
-
I tried this SELECT "date", convert_tz("date", 'GMT', 'CET') FROM "database" but it did not work. Do you know how I would implement this? – MathMan 99 Jan 10 '22 at 15:39
-
Telling us it doesn't work is not very helpful. Post your actual code and the result/error message. The below answer should work. – Andrew Jan 10 '22 at 17:41
-
Here is the error: ERROR: function convert_tz(timestamp with time zone, unknown, unknown) does not exist LINE 1: select "date", convert_tz(date, 'GMT', 'CET') ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 16. I think that's because my date includes a time zone? (the +00 part) Could that be the issue? – MathMan 99 Jan 10 '22 at 19:46
1 Answers
0
Apparently you need to specify timezone offset in the function, not timezone names:
convert_tz(`date`,'+00:00','+10:00')
Andrew
- 8,445
- 3
- 28
- 46
-
Would this take into account daylight savings? Also, I tried your suggestion and got this error: function convert_tz(unknown, unknown, unknown) does not exist. I think that's because my timestamp has a timezone as well – MathMan 99 Jan 11 '22 at 02:38
-
-