Friday, January 6, 2017

How to group by date in different time zone when it's in UTC

SELECT ... FROM ... GROUP BY DATE(CONVERT_TZ(start_dt,'UTC','America/Vancouver'))

Note that you need to load the timezone data into MySQL before this will work.

Apart from Windows environment, You can set Time Zone by

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

In Windows environment,

1. download Time zone description tables from

2. Stop MySQL server

3. Put then inside Mysql installation package (ie. C:\Program Files\MySQL\data\mysql)`

4. Start MySQL server

..Your work is finished..

If still you are getting NULL for CONVERT_TZ Download these database tables and insert it into mysql database


No comments: