13

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?

mpen
  • 272,448
  • 266
  • 850
  • 1,236

3 Answers3

22

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.

Community
  • 1
  • 1
mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • 4
    Or use a numeric offset as the third parameter for `CONVERT_TZ()` ([manual](http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_convert-tz)), so that it does not require the time zone tables, eg. `CONVERT_TZ('2004-01-01 12:00:00','UTC','+10:00');` – RandomSeed Jan 19 '15 at 12:31
  • 4
    @RandomSeed The problem with that is DST. The offset at the start of the month may not be the same as at the end of the month. – mpen Jan 19 '15 at 18:22
  • 5
    @RandomSeed You made little mistake in You example. It should be: `CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');` because using `UTC` instead of `+00:00` still cause `NULL` as a result. – instead Feb 13 '18 at 10:36
1

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...

Brian North
  • 1,398
  • 1
  • 14
  • 19
  • 1
    You just need to remember that tzdata is not set in stone and once you have some dates converted to timestamps you won't necessary have them back correctly once the timezone rules are changed. – Oleg Komarov Jan 18 '15 at 22:42
0

you may intermediately switch to the users tz:

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

just found: How do I set the time zone of MySQL?

Community
  • 1
  • 1
Axel Amthor
  • 10,980
  • 1
  • 25
  • 44
  • Changing the timezone won't actually affect my `DATETIME` fields will it? – mpen Jan 18 '15 at 21:48
  • 5
    Unfortunately it wont. From the MySQL doc: "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." – Brian North Jan 18 '15 at 21:50
  • no! just try `SET @@session.time_zone = "+03:00"; select now() from dual;` It won't change your clock ... – Axel Amthor Jan 18 '15 at 21:51
  • @AxelAmthor That's a problem then. Grouping by `DATE()` will be exactly the same as if I didn't set the timezone. – mpen Jan 18 '15 at 21:52
  • 1
    As far as I can tell, the only way to get MySQL to do locale-aware operations is to do everything with `TIMESTAMP` columns; then the session timezone will come into play for grouping, etc. – Brian North Jan 18 '15 at 21:53