Mysql – How to store a datetime in MySQL with timezone info

datetimeMySQLtimezone

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.

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

Related Topic