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 http://dev.mysql.com/downloads/timezones.html

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 http://www.4shared.com/folder/Toba2qu-/Mysql_timezone.html

Reference:

http://stackoverflow.com/questions/28015068/how-to-group-by-date-accounting-for-timezones-and-dst

http://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465#14454465

http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html