How to store a datetime in MySQL with timezone info

Question

I have thousands of photos that were taken in Tanzania and I want to store the date and time each photo was taken in a MySQL database  The server  however  is located in the U S  and I run into problems when I try to store a Tanzanian date-time that falls within the  invalid  hour during spring Daylight Savings time  in the U S    Tanzania doesn t do DST  so the time is an actually valid time    Additional complications are that there are collaborators from many different timezones who will need to access the date-time values stored in the database  I want them to always come out as Tanzanian time and not in the local times that various collaborator are in    I m reluctant to set session times because I know that there will be problems when someone sometime forgets to set a session time and gets the times out all wrong  And I do not have authority to change anything about the server   I ve read   Daylight saving time and time zone best practices and MySQL datetime fields and daylight savings time -- how do I reference the  quot extra quot  hour  and Storing datetime as UTC in PHP MySQL  But none of them seems to address my particular problem  I m not an SQL expert  is there a way to specify timezone when setting DATETIMEs  I haven t seen one  Otherwise  any suggestions on how to approach this issue is greatly appreciated   Edit  Here s an example of the problem I m running into  I send the command   INSERT INTO Images  CaptureEvent  SequenceNum  PathFilename  TimestampJPG   VALUES  122 1  S2 B04 B04 R1 IMAG0148 JPG   2011-03-13 02 49 10     And I get the error   Error 1292  Incorrect datetime value   2011-03-13 02 49 10  for column  TimestampJPG    This date and time exists in Tanzania  but not in the U S   where the database is

User · Answer

MySQL stores DATETIME without timezone information  Let s say you store  2019-01-01 20 00 00  into a DATETIME field  when you retrieve that value you re expected to know what timezone it belongs to   So in your case  when you store a value into a DATETIME field  make sure it is Tanzania time   Then when you get it out  it will be Tanzania time  Yay   Now  the hairy question is  When I do an INSERT UPDATE  how do I make sure the value is Tanzania time   Two cases    You do INSERT INTO table  dateCreated  VALUES  CURRENT TIMESTAMP or NOW     You do INSERT INTO table  dateCreated  VALUES      and specify the current time from your application code    CASE  1  MySQL will take the current time  let s say that is  2019-01-01 20 00 00  Tanzania time  Then MySQL will convert it to UTC  which comes out to  2019-01-01 17 00 00   and store that value into the field   So how do you get the Tanzania time  which is  20 00 00   to store into the field   It s not possible   Your code will need to expect UTC time when reading from this field   CASE  2  It depends on what type of value you pass as     If you pass the string  2019-01-01 20 00 00   then good for you  that s exactly what will be stored to the DB   If you pass a Date object of some kind  then it ll depend on how the db driver interprets that Date object  and ultimate what  YYYY-MM-DD HH mm ss  string it provides to MySQL for storage   The db driver s documentation should tell you

User · Answer

You said      I want them to always come out as Tanzanian time and not in the local times that various collaborator are in    If this is the case  then you should not use UTC   All you need to do is to use a DATETIME type in MySQL instead of a TIMESTAMP type   From the MySQL documentation      MySQL converts TIMESTAMP values from the current time zone to UTC for storage  and back from UTC to the current time zone for retrieval   This does not occur for other types such as DATETIME     If you are already using a DATETIME type  then you must be not setting it by the local time to begin with   You ll need to focus less on the database  and more on your application code - which you didn t show here   The problem  and the solution  will vary drastically depending on language  so be sure to tag the question with the appropriate language of your application code

User · Answer

All the symptoms you describe suggest that you never tell MySQL what time zone to use so it defaults to system s zone  Think about it  if all it has is  2011-03-13 02 49 10   how can it guess that it s a local Tanzanian date   As far as I know  MySQL doesn t provide any syntax to specify time zone information in dates  You have to change it a per-connection basis  something like   SET time zone    EAT     If this doesn t work  to use named zones you need that the server has been configured to do so and it s often not the case  you can use UTC offsets because Tanzania does not observe daylight saving time at the time of writing but of course it isn t the best option   SET time zone     03 00

User · Answer

I once also faced such an issue where i needed to save data which was used by different collaborators and i ended up storing the time in unix timestamp form which represents the number of seconds since january 1970 which is an integer format  Example todays date and time in tanzania is Friday  September 13  2019 9 44 01 PM which when store in unix timestamp would be 1568400241  Now when reading the data simply use something like php or any other language and extract the date from the unix timestamp  An example with php will be   echo date  m d Y   1568400241     This makes it easier even to store data with other collaborators in different locations  They can simply convert the date to unix timestamp with their own gmt offset and store it in a integer format and when outputting this simply convert with a

User · Answer

None of the answers here quite hit the nail on the head      How to store a datetime in MySQL with timezone info   Use two columns  DATETIME  and a VARCHAR to hold the time zone information  which may be in several forms   A timezone or location such as America New York is the highest data fidelity   A timezone abbreviation such as PST is the next highest fidelity   A time offset such as -2 00 is the smallest amount of data in this regard   Some key points    Avoid TIMESTAMP because it s limited to the year 2038  and MySQL relates it to the server timezone  which is probably undesired  A time offset should not be stored naively in an INT field  because there are half-hour and quarter-hour offsets      If it s important for your use case to have MySQL compare or sort these dates chronologically  DATETIME has a problem     2009-11-10 11 00 00 -0500  is before  2009-11-10 10 00 00 -0700  in terms of  instant in time   but they would sort the other way when inserted into a DATETIME   You can do your own conversion to UTC   In the above example  you would then have  2009-11-10 16 00 00  and  2009-11-10 17 00 00  respectively  which would sort correctly   When retrieving the data  you would then use the timezone info to revert it to its original form   One recommendation which I quite like is to have three columns    local time DATETIME utc time DATETIME  time zone VARCHAR X  where X is appropriate for what kind of data you re storing there    I would choose 64 characters for timezone location     An advantage to the 3-column approach is that it s explicit  with a single DATETIME column  you can t tell at a glance if it s been converted to UTC before insertion     Regarding the descent of accuracy through timezone abbreviation offset    If you have the user s timezone location such as America Juneau  you can know accurately what the wall clock time is for them at any point in the past or future  barring changes to the way Daylight Savings is handled in that location    The start end points of DST  and whether it s used at all  are dependent upon location  so this is the only reliable way  If you have a timezone abbreviation such as MST   Mountain Standard Time  or a plain offset such as -0700  you will be unable to predict a wall clock time in the past or future   For example  in the United States  Colorado and Arizona both use MST  but Arizona doesn t observe DST   So if the user uploads his cat photo at 14 00 -0700 during the winter months  was he in Arizona or California   If you added six months exactly to that date  would it be 14 00 or 13 00 for the user    These things are important to consider when your application has time  dates  or scheduling as core function     References    MySQL Date Time Reference The Proper Way to Handle Multiple Time Zones in MySQL  Disclosure  I did not read this whole article

[mysql] How to store a datetime in MySQL with timezone info

Examples related to mysql

Examples related to datetime

Examples related to timezone