How to store a datetime in MySQL with timezone info

88
votes

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 "extra" 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.

5
mysqldatetimetimezone
You shouldn't want to store time zone info in database. Store all date/time data as UTC and always make time zone offset adjustment on the application layer. – marekful
@MarcellFülöp: I keep seeing people say "store it as UTC" but I don't understand what that means. How can I store a date time "as UTC"? As far as I can tell all I can do is insert something of the form YYYY-MM-DD HH:MM:SS. Where do I tell the database that it's UTC? – mkosmala
You don't. MySQL by default will use the system time zone internally but it's possible to define a different time zone for the MySQL server globally or even per transaction. When you insert a date, it is not possible to define the time zone along with the date string in MySQL. A date like '2013-11-10 00:00' refers to a point in time since the epoch. You store it and you know what time zone your server is in. Then when you retrieve it, you can make the necessary adjustments to transform that date form the server's time zone to the client's. – marekful
@marekful Nope, it depends. I want to filter out photos that were taken at dawn, and UTC won't help in this case. I would like to save both the UTC timestamp and the timezone offset. – Arnie97
The best practice, IMO and what many experienced engineers follow, is still to store all date/time values as UTC (a.k.a. Zulu). Configure the db server to be in UTC regardless of physical location. Then, what you want to do in this particular case is to know that you want to find times of day that correspond to dawn in Tanzania. So you add the logic in your program code. Create a time zone instance with Tanzania's time zone and filter all date/time values fetched from the database through it. It will do the rest and represent all values in Tanzania time. It will also take care of DST. – marekful

5 Answers

78
votes

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.

33
votes

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:

8
votes

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:

  1. You do INSERT INTO table (dateCreated) VALUES (CURRENT_TIMESTAMP or NOW()).

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

6
votes

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';
0
votes

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