What is best practice for timezone handling in MySQL?

Question

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?

Answer 1

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