How to group by date, given time zones and DST?

Question

I have a bunch of events stored in my database. Each event has a start time, which is stored DATETIMEin UTC format. Each user can select their time zone.

I want to display a calendar that shows the number of events on each day of the month. I can’t GROUP BY DATE(start_dt)because the custom day [required] does not start at 00:00:00 UTC. In addition, a custom time zone may have a DST switch in the middle of the month.

Is my only option to receive every single event for a given month, and then group and count them in my server language?

Answer 1

You can try

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

Please note that you need to load timezone data into MySQL before this works.

Answer 2

you can switch between users on tz:

  SET @@session.time_zone = "+05:00"; 
  select ... from .. group by MONTH(datefield);
  SET @@session.time_zone = @@global.time_zone;

just found: How to set MySQL timezone?

Answer 3

, DATETIME :

, , UTC_TIMESTAMP() DATE, TIME DATETIME. , UTC; TIMESTAMP. , , , DATE, TIME DATETIME, UTC, , . [MySQL Docs]

, , / , TIMESTAMP. - TIMESTAMP / @@session.time_zone, , .

, , DATETIME TIMESTAMP MySQL , , , ...