Should MySQL have its timezone set to UTC

Question

Follow up question of https   serverfault com questions 191331 should-servers-have-their-timezone-set-to-gmt-utc  Should the MySQL timezone be set to UTC or should it be set to be the same timezone as the server or PHP is set   If it is not UTC   What are the pros and cons

User · Answer

This is a working example   jdbc mysql   localhost 3306 database useUnicode yes amp characterEncoding UTF-8 amp serverTimezone Europe Moscow

User · Answer

It seems that it does not matter what timezone is on the server as long as you have the time set right for the current timezone  know the timezone of the datetime columns that you store  and are aware of the issues with daylight savings time   On the other hand if you have control of the timezones of the servers you work with then you can have everything set to UTC internally and never worry about timezones and DST    Here are some notes I collected of how to work with timezones as a form of cheatsheet for myself and others which might influence what timezone the person will choose for his her server and how he she will store date and time    MySQL Timezone Cheatsheet  Notes     Changing the timezone will not change the stored datetime or timestamp  but it will select a different datetime from timestamp columns Warning  UTC has leap seconds  these look like  2012-06-30 23 59 60  and can  be added randomly  with 6 months prior notice  due to the slowing of the earths rotation GMT confuses seconds  which is why UTC was invented  Warning  different regional timezones might produce the same datetime value due to daylight savings time The timestamp column only supports dates 1970-01-01 00 00 01 to 2038-01-19 03 14 07 UTC  due to a limitation  Internally a MySQL timestamp column is stored as UTC but when selecting a date MySQL will automatically convert it to the current session timezone   When storing a date in a timestamp  MySQL will assume that the date is in the current session timezone and convert it to UTC for storage  MySQL can store partial dates in datetime columns  these look like  2013-00-00 04 00 00  MySQL stores  0000-00-00 00 00 00  if you set a datetime column as NULL  unless you specifically set the column to allow null when you create it  Read this   To select a timestamp column in UTC format  no matter what timezone the current MySQL session is in   SELECT  CONVERT TZ  timestamp field     session time zone    00 00   AS  utc datetime   FROM  table name    You can also set the sever or global or current session timezone to UTC and then select the timestamp like so   SELECT  timestamp field  FROM  table name    To select the current datetime in UTC   SELECT UTC TIMESTAMP    SELECT UTC TIMESTAMP  SELECT CONVERT TZ NOW      session time zone    00 00      Example result  2015-03-24 17 02 41  To select the current datetime in the session timezone  SELECT NOW    SELECT CURRENT TIMESTAMP  SELECT CURRENT TIMESTAMP      To select the timezone that was set when the server launched  SELECT   system time zone    Returns  MSK  or   04 00  for Moscow time for example  there is  or was  a MySQL bug where if set to a numerical offset it would not adjust the Daylight savings time   To get the current timezone  SELECT TIMEDIFF NOW    UTC TIMESTAMP     It will return 02 00 00 if your timezone is  2 00   To get the current UNIX timestamp  in seconds    SELECT UNIX TIMESTAMP NOW     SELECT UNIX TIMESTAMP      To get the timestamp column as a UNIX timestamp  SELECT UNIX TIMESTAMP  timestamp   FROM  table name    To get a UTC datetime column as a UNIX timestamp  SELECT UNIX TIMESTAMP CONVERT TZ  utc datetime     00 00     session time zone   FROM  table name    Get a current timezone datetime from a positive UNIX timestamp integer  SELECT FROM UNIXTIME  unix timestamp int   FROM  table name    Get a UTC datetime from a UNIX timestamp  SELECT CONVERT TZ FROM UNIXTIME  unix timestamp int      session time zone    00 00    FROM  table name    Get a current timezone datetime from a negative UNIX timestamp integer  SELECT DATE ADD  1970-01-01 00 00 00  INTERVAL -957632400 SECOND     There are 3 places where the timezone might be set in MySQL   Note  A timezone can be set in 2 formats     an offset from UTC    00 00     10 00  or  -6 00  as a named time zone   Europe Helsinki    US Eastern   or  MET       Named time zones can be used only if the time zone information tables   in the mysql database have been created and populated    in the file  my cnf   default time zone   00 00    or  timezone  UTC      global time zone variable  To see what value they are set to  SELECT   global time zone    To set a value for it use either one   SET GLOBAL time zone     8 00   SET GLOBAL time zone    Europe Helsinki   SET   global time zone   00 00       session time zone variable  SELECT   session time zone    To set it use either one   SET time zone    Europe Helsinki   SET time zone     00 00   SET   session time zone     00 00     both    global time zone variable  and    session time zone variable  might return  SYSTEM  which means that they use the timezone set in  my cnf    For timezone names to work  even for default-time-zone  you must setup your timezone information tables need to be populated  http   dev mysql com doc refman 5 1 en time-zone-support html      Note  you can not do this as it will return NULL   SELECT  CONVERT TZ  timestamp field   TIMEDIFF NOW    UTC TIMESTAMP     00 00   AS  utc datetime   FROM  table name    Setup mysql timezone tables  For CONVERT TZ to work  you need the timezone tables to be populated  SELECT   FROM mysql  time zone    SELECT   FROM mysql  time zone leap second    SELECT   FROM mysql  time zone name    SELECT   FROM mysql  time zone transition    SELECT   FROM mysql  time zone transition type      If they are empty  then fill them up by running this command  mysql tzinfo to sql  usr share zoneinfo   mysql -u root -p mysql   if this command gives you the error  data too long for column  abbreviation  at row 1   then it might be caused by a NULL character being appended at the end of the timezone abbreviation  the fix being to run this  mysql tzinfo to sql  usr share zoneinfo   mysql -u root -p mysql  if the above gives error  data too long for column  abbreviation  at row 1   mysql tzinfo to sql  usr share zoneinfo  gt   tmp zut sql  echo  SET SESSION SQL MODE         gt   tmp mysql tzinfo to sql cat  tmp zut sql  gt  gt   tmp mysql tzinfo to sql  mysql --defaults-file  etc mysql my cnf --user verifiedscratch -p mysql  lt   tmp mysql tzinfo to sql    make sure your servers dst rules are up to date zdump -v Europe Moscow   grep 2011 https   chrisjean com updating-daylight-saving-time-on-linux    See the full DST  Daylight Saving Time  transition history for every timezone  SELECT  tzn Name AS tz name  tztt Abbreviation AS tz abbr  tztt Is DST AS is dst  tztt  Offset  AS  offset   DATE ADD  1970-01-01 00 00 00  INTERVAL tzt Transition time SECOND   AS transition date FROM mysql  time zone transition  tzt INNER JOIN mysql  time zone transition type  tztt USING Time zone id  Transition type id  INNER JOIN mysql  time zone name  tzn USING Time zone id  -- WHERE tzn Name LIKE  Europe Moscow  -- Moscow has weird DST changes ORDER BY tzt Transition time ASC   CONVERT TZ also applies any necessary DST changes based on the rules in the above tables and the date that you use   Note  According to the docs  the value you set for time zone does not change  if you set it as   01 00  for example  then the time zone will be set as an offset from UTC  which does not follow DST  so it will stay the same all year round   Only the named timezones will change time during daylight savings time   Abbreviations like CET will always be a winter time and CEST will be summer time while  01 00 will always be UTC time   1 hour and both won t change with DST   The system timezone will be the timezone of the host machine where mysql is installed  unless mysql fails to determine it   You can read more about working with DST here  related questions    How do I set the time zone of MySQL  MySql - SELECT TimeStamp Column in UTC format How to get Unix timestamp in MySQL from UTC time  Converting Server MySQL TimeStamp To UTC https   dba stackexchange com questions 20217 mysql-set-utc-time-as-default-timestamp How do I get the current time zone of MySQL  MySQL datetime fields and daylight savings time -- how do I reference the  quot extra quot  hour  Converting negative values from FROM UNIXTIME   Sources    https   bugs mysql com bug php id 68861 http   dev mysql com doc refman 5 0 en date-and-time-functions html http   dev mysql com doc refman 5 1 en datetime html http   en wikipedia org wiki Coordinated Universal Time http   shafiqissani wordpress com 2010 09 30 how-to-get-the-current-epoch-time-unix-timestamp  https   web ivy net  carton rant MySQL-timezones txt

User · Answer

The pros and cons are pretty much identical It depends on whether you want this or not   Be careful  if MySQL timezone differs from your system time  for instance PHP   comparing the time or printing to the user will involve some tinkering

User · Answer

PHP and MySQL have their own default timezone configurations  You should synchronize time between your data base and web application  otherwise you could run some issues   Read this tutorial  How To Synchronize Your PHP and MySQL Timezones

User · Answer

How about making your app agnostic of the server s timezone  Owing to any of these possible scenarios   You might not have control over the web database server s timezone settings You might mess up and set the settings incorrectly There are so many settings as described in the other answers  and so many things to keep track of  that you might miss something An update on the server  or a software reset  or another admin  might unknowing reset the servers  timezone to the default - thus breaking your application  All of the above scenarios give rise to breaking of your application s time calculations  Thus it appears that the better approach is to make your application work independent of the server s timezone  The idea is simply to always create dates in UTC before storing them into the database  and always re-create them from the stored values in UTC as well  This way  the time calculations won t ever be incorrect  because they re always in UTC  This can be achieved by explicity stating the DateTimeZone parameter when creating a PHP DateTime object  On the other hand  the client side functionality can be configured to convert all dates times received from the server to the client s timezone  Libraries like moment js make this super easy to do  For example  when storing a date in the database  instead of using the NOW   function of MySQL  create the timestamp string in UTC as follows     Storing dates  date   new DateTime  now   new DateTimeZone  UTC      sql    insert into table name  date column  values   quot      date     quot         Retreiving dates  sql    select date column from table name where condition    dateInUTC   new DateTime  date string from db  new DateTimeZone  UTC      You can set the default timezone in PHP for all dates created  thus eliminating the need to initialize the DateTimeZone class every time you want to create a date

[mysql] Should MySQL have its timezone set to UTC?

Examples related to mysql

Examples related to time

Examples related to utc