Can MySQL convert a stored UTC time to local timezone

Question

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement  Let s say you have some data with a timestamp  UTC   CREATE TABLE  SomeDateTable       id     int 11  NOT NULL auto increment     value  float NOT NULL default  0      date   datetime NOT NULL default  0000-00-00 00 00 00     PRIMARY KEY    id      Then when I  quot select value  date from SomeDateTable quot    I of course get all the dates as in their stored UTC form  But let s say that I would like to have them in another timezone  with DST   can I then add some magic to the select query so that I get all the dates back in the selected timezone   quot select value  TIMEZONE date   quot Europe Berlin quot   from SomeDateTable quot    Or must I do this in some other layer on top  like in some php code   it seems to be how most people have solved this problem    If your MySQL installation allows you to use CONVERT TZ it is a very clean solution  this example shows how to use it  SELECT CONVERT TZ   2010-01-01 12 00    UTC    Europe Stockholm     However I don t know if this is a good way since some MySQL installation is missing this function  use with care

User · Answer

One can easily use   CONVERT TZ your timestamp column name   UTC    your desired timezone name     For example   CONVERT TZ timeperiod   UTC    Asia Karachi     Plus this can also be used in WHERE statement and to compare timestamp i would use the following in Where clause   WHERE CONVERT TZ timeperiod   UTC      this- gt timezone    NOT BETWEEN   timeperiods  today start    AND   timeperiods  today end

User · Answer

I am not sure what math can be done on a DATETIME data type  but if you are using PHP  I strongly recommend using the integer-based timestamps  Basically  you can store a 4-byte integer in the database using PHP s time   function  This makes doing math on it much more straightforward

User · Answer

I propose to use  SET time zone    proper timezone     being done once right after connect to database  and after this all timestamps will be converted automatically when selecting them

User · Answer

select convert tz now     session time zone   03 00     For get the time only use   time convert tz now     session time zone   03 00

User · Answer

Yup  there s the convert tz function

User · Answer

1  Correctly setup your server  On server  su to root and do this    mysql tzinfo to sql  usr share zoneinfo   mysql mysql   Note that the command at the end is of course mysql   and  you re sending it to a table which happens to have the same name  mysql   Next  you can now   ls  usr share zoneinfo   Use that command to see all the time zone info on ubuntu or almost any unixish server   BTW that s the convenient way to find the exact official name of some time zone   2  It s then trivial in mysql  For example mysql gt  select ts  CONVERT TZ ts   UTC    Pacific Tahiti   from example table    --------------------- -----------------------------------------    ts                    CONVERT TZ ts   UTC    Pacific Tahiti      --------------------- -----------------------------------------    2020-10-20 16 59 57   2020-10-20 06 59 57                         2020-10-20 17 02 59   2020-10-20 07 02 59                         2020-10-20 17 30 08   2020-10-20 07 30 08                         2020-10-20 18 36 29   2020-10-20 08 36 29                         2020-10-20 18 37 20   2020-10-20 08 37 20                         2020-10-20 18 37 20   2020-10-20 08 37 20                         2020-10-20 19 00 18   2020-10-20 09 00 18                        --------------------- -----------------------------------------

User · Answer

For those unable to configure the mysql environment  e g  due to lack of SUPER access  to use human-friendly timezone names like  America Denver  or  GMT  you can also use the function with numeric offsets like this   CONVERT TZ date   00 00   -07 00

[mysql] Can MySQL convert a stored UTC time to local timezone?

Examples related to mysql

Examples related to sql

Examples related to timezone

Examples related to convert-tz