6

This has been asked before but not the answer I am looking for. I am storing all my dates in MYSQL in UTC/GMT. When I extract data fora user that references time is it better to use the CONVERT_TZ construct...

SELECT CONVERT_TZ(mytime,'UTC',usertimezone) as mytime FROM table

or is it better to temporarily set the session zone in Mysql and then do normal queries?

SET time_zone = usertimezone;

And if I use the second, do I just do that once for each user session or if I am not using a persistent open, do I need to set it before each query?

Doug Wolfgram
  • 2,064
  • 4
  • 27
  • 42
  • Any reason not to let this be done in the client instead? – Jon Skeet Oct 08 '13 at 17:54
  • It is a lot of work to process every time value before display. Trying to do a minimum of coding. :) – Doug Wolfgram Oct 08 '13 at 17:55
  • You store the user's timezone and then use that in converting UTC to their timezone. – Kermit Oct 08 '13 at 17:55
  • 2
    @DougWolfgram: Well you're only moving the work to the database, for no particularly obvious reason. The work still needs to be done, and it shouldn't be significantly more code to do this at the client side... – Jon Skeet Oct 08 '13 at 17:57
  • Does this help:- http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices ? – Rahul Tripathi Oct 08 '13 at 17:58
  • I think I want to always store in UTC, but when I am using date math for a user (give me the last 30 days worth of data) I should used the CONVERT_TZ so I get HIS last 30 days. Otherwise, if I am just formatting it for display, do the conversion at the display end. Does that make sense? – Doug Wolfgram Oct 08 '13 at 18:16

2 Answers2

9

  • Use TIMESTAMP if you want MySQL to do the conversion based on the time_zone setting of the current session.

  • Use DATETIME if you are returning UTC to your application for it to handle the conversion there. (This would be my preference.)

  • Don't try to mix these up. DATETIME will not do anything with the time_zone setting, and TIMESTAMP cannot be assumed to be UTC when it is returned to your application unless you are absolutely sure that time_zone is set to UTC.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
2

If your data is stored in TIMESTAMP type columns, then you should SET time_zone and MySQL will automatically convert to/from UTC on retrieval/insertion—you don't need to do anything more. This is the recommended approach.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • I rarely use Timestamp, but rather, datetime. This application is high volume data collection and I want to write several records with the exact same timestamp. – Doug Wolfgram Oct 08 '13 at 17:56
  • 1
    @DougWolfgram: You can still provide a given value to a `TIMESTAMP` field ([automatic initialisation and updating](http://dev.mysql.com/doc/en/timestamp-initialization.html) are *optional*, albeit active by default). `DATETIME` is a different beast altogether and does not store any notion of timezone (so MySQL *cannot* perform any implicit conversion to the session `time_zone`). – eggyal Oct 08 '13 at 17:58
  • I dunno. When I use set_timezone to set my session to UTC it appears to force datetime inserts to convert. – Doug Wolfgram Oct 08 '13 at 18:18
  • it’s a signed 32 bit number, the minimum date is 1970-01-01 00:00:00 and the maximum is 2038-01-19 03:14:07 UTC (that’s 2^31-1), so i don't think this gonna help you a lot – Virendra Singh Rathore Apr 17 '19 at 07:25
  • @VirendraSinghRathore: what on Earth does that have to do with this question, or my answer? – eggyal Apr 17 '19 at 07:47
  • @eggyal I was referring TIMESTAMP limitation for storing dates. – Virendra Singh Rathore Apr 17 '19 at 08:33
  • @VirendraSinghRathore: I know what you were referring to, but what bearing does it have on this question/answer? – eggyal Apr 17 '19 at 08:33
  • Calm down I did not find storing dates as timestamp is a best practice to handle timezone. I agree your solutions is right if we store date as timestamp. – Virendra Singh Rathore Apr 17 '19 at 08:43