How to group by date, accounting for timezones and DST?

Question

I have a bunch of events stored in my database. Each event has a start time, stored as DATETIME in UTC. Each user can choose his/her own timezone.

I want to display a calendar which just shows the number of events on each day of the month. I can't GROUP BY DATE(start_dt) because the user's day does not [necessarily] start at 00:00:00 UTC. Moreover, the user's timezone may have a DST switch in the middle of the month.

Is my only option then to fetch every single event for the given month and then group and count them with my server-side language?

Answer 1

You can try

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.

Answer 2

Unfortunately DATETIME columns are stored without timezone information:

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back. [MySQL Docs]

Fortunately, this does mean that date/time functions should work with timezone support, but only on TIMESTAMP columns. Once in a TIMESTAMP column, the date/time functions should be able to take into account @@session.time_zone, which you can change around as necessary to get different timezones.

So, your choices are to keep your tables with DATETIME columns, and do all the grouping yourself, or convert those columns to TIMESTAMP and let MySQL do it - but if the columns are already in differing and inconsistent timezones, you'll have your work cut out for you...