How to store datetimes in UTC and local timezone
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.
Answer
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.