CODEWITHSUNDEEP
mysqlsql
user2146933
user2146933

Reputation: 407

convert_tz returns null even after loading time zone tables

I have a MySQL database (hosted on Ubuntu) that has a table with a time zone. This date is in the UTC time zone. I have an application that is using this data but needs to convert the time from UTC to PST to display the data in a useful format.

This can probably be changed on the application level, but I don't have the time to do that currently, so what I want to do is use convert_tz to get the correct time zone until I have time to change the application to convert the time zone.

However, whenever I do something like

SELECT id, 
       category, 
       convert_tz(create_datetime, 'UTC', 'PST') as create_datetime
FROM table

I get a result like

1, category, NULL

I know that I need to load the time zone tables, so I did, running this command:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

After that I restarted the mysql service. However, convert_tz still returns NULL. Can someone give me a hand?

Thanks!

Upvotes: 6

Views: 4256

Answers (2)

Adarsh Raj
Adarsh Raj

Reputation: 301

Make sure that your create_datetime colum is of type TIMESTAMP.

Please refer to the official MySQL Reference Manual:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz

Example:

SELECT CONVERT_TZ('2004-01-01 12:00:00', 'GMT', 'MET');

Upvotes: 2

Dave Schweisguth
Dave Schweisguth

Reputation: 37627

When I had this problem it was because I used the wrong time zone name. I incorrectly inferred from the time zone used in the example in the MySQL documentation, MET, that 3-letter US time zones such as PST would work. That's not the case: US time zones are named 'PST8PDT' and the like. So whereas

select convert_tz('2016-10-16 06:23:00', 'UTC', 'PST');

returns NULL,

select convert_tz('2016-10-16 06:23:00', 'UTC', 'PST8PDT');

returns the converted time.

You can see the time zone names in your MySQL instance with

select Name from mysql.time_zone_name;

Upvotes: 1

Related Questions