All geek questions in one place

What is best for processing timezones in MySQL?

This has been asked before, but not the answer I'm looking for. I save all my dates in MYSQL in UTC / GMT. When I retrieve data for a user that refers to time, it is 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 run normal queries?

 SET time_zone = usertimezone;

And if I use the second one, I just do it once for each user session, or if I do not use a constant open, do I need to install it before each request?

+6
source share
2 answers

If your data is stored in TIMESTAMP type columns, you must SET time_zone , and MySQL will automatically convert to / from UTC when searching / inserting - you no longer need to do anything. This is the recommended approach.

+3
source

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

  • Use DATETIME if you are returning UTC to your application so that it handles the conversion. (That would be my advantage.)

  • Do not try to mix them. DATETIME will not do anything with the time_zone setting, and TIMESTAMP cannot be considered UTC when it returns to your application unless you are absolutely sure that time_zone set to UTC.

+7
source

Source: https://habr.com/ru/post/955515/

More articles:


All Articles