How to store datetimes in UTC and local timezone
Asked Answered
B

3

7

What is a practical way to store datetimes so that I can let users view/query data as of their own local time while keeping information about the original datetime.

Basically, users want to be able to query (as of their own local time) data collected from systems in various time zones. But occasionally, they want to know that the data was created at, say, 18:00 in the original system. It helps when users from different parts of the world communicate about the same event.

User1: What? We don't have any data for 20:00
User2: Dude, it says 20:00 right there on my screen.
User1: Wait, what timezone are you? What's the UTC-time?
User2: What is UTC? Is that something with computers?
User1: OMFG! *click*

I'm looking for advice on how to store the data.

I'm thinking of storing all datetimes in UTC and adding an additional column containing original timezone name, in a form that lets me use mysql CONVERT_TZ, or the counterpart in Java. The application would then convert dates entered by the user into UTC and I can easily query the database. All dates can also easily be converted to the users local time in the application. Using the original time zone column I also would be able to display the original datetime.

However, this means that for each datetime I have, I need an additional column...

start_time_utc datetime
start_time_tz  varchar(64)
end_time_utc   datetime
end_time_tz    varchar(64)

Am I on the right track here?

Would anyone who have worked with such data share their experiences?

(I will be using MySQL 5.5 CE)

Update 1

Data will be delivered in xml files where each entry has a datetime in some local time zone. So there will only be one inserting process, running in one place.

Once loaded in the database, it will be presented in some web application to users in different time zones. For the majority of the use cases the data of interest did also originate from the same time zone as the user looking at the data. For some of the more complicated use cases, a series of events are interconnected and span multiple time zones. Hence, users want to be able to talk about the events in order to investigate probable causes/consequences in the other's time. Not UTC, not their own local time.

Backfill answered 9/1, 2011 at 21:14 Comment(8)
For the problem at hand, I believe your solution is a good one.Kano
Doesn't MySQL have DateTimeOffset, like MSSQL (a data type specifying a timestamp and its offset from UTC)? If it doesn't, then your solution is the closest equivalent, differing only in the amount of fields needed (1 vs 2).Latea
No, MySQL does not support TIMESTAMP WITH TIMEZONE (which is the ANSI data type for this)Nellie
@horse, I was looking for something like that (I've experimented with it in Oracle) but couldn't find any in MySQL. Do you have experience with "timezoned" data in MySQL?Backfill
no I don't. Luckily I don't need to use MySQL very often.Nellie
@Ronnis, sorry to resurrect, but what did you end up doing here?Hirai
@epoch, we ended up storing pairs of columns, like in my example.Backfill
@Ronnis, thanks man, now I need to find out an architecture for 500+ tables, if you have any input, my question is here: #28147021 :)Hirai
M
6

As the users can live in different timezones and even can move from one timezone to other, the best practice is to store the date and time in UTC and convert to user's timezone at the time of displaying.

Meissen answered 20/1, 2013 at 7:16 Comment(0)
S
4

The manual has a section just for this, about timestamp:

TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described in Section 9.6, “MySQL Server Time Zone Support”. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

So you can use: SET time_zone = timezone; on the client to set the time zone. Then all queries would translate the timestamp to the correct time zone. No need to do anything complex in Java, except for setting the time zone (I think might even be a parameter in the JDBC connection string)

Subsistent answered 10/1, 2011 at 15:11 Comment(2)
I'd still need to store the original timezone in order to display it in another local timezone (see the dialogue).Backfill
Maybe I misunderstood. So like Eldad said, storing the original timezone with the column is your only option...Subsistent
E
0

You can always get the zulu time as base for all your calculations.

Exscind answered 28/9, 2011 at 16:46 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.