How to store data in UTC and local time zone

Question

What is a practical way of storing data so that I can allow users to view / query data according to their local time, storing information about the original datetime.

Basically, users want to be able to request (at their own local time) data collected from systems in different time zones. But sometimes they want to know that the data was created, for example, at 18:00 in the source system. This helps when users from different parts of the world exchange information 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 the UTC-time? User2: What is UTC? Is that something with computers? User1: OMFG! *click* 

I am looking for advice on how to store data.

I am going to store all datetime in UTC and add an extra column containing the original timezone name in a form that allows me to use mysql CONVERT_TZ or an analogy in Java. The application then converts the dates entered by the user into UTC, and I can easily query the database. All dates can also be easily converted to the user's local time in the application. Using the original timezone column, I can also display the original datetime.

However, this means that for every day and time I need an extra 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?

Does anyone who has worked with such data share their experiences?

(I will use MySQL 5.5 CE)

Update 1

The data will be delivered in xml files, where each record has a date-time in some local time zone. Thus, there will be only one insertion process, working in one place.

After loading into the database, it will be presented to users in different time zones in some web applications. For most use cases, the data of interest also came from the same time zone as the user viewing the data. For some more complex use cases, a series of events are interrelated and span multiple time zones. Therefore, users want to be able to talk about events in order to investigate the likely causes / consequences at another time. Not UTC, not their local time.

Answer 1

Since users can live in different time zones and can even switch from one time zone to another, it is best to store the date and time in UTC and convert it to the user's time zone during display.

Answer 2

The manual has a section just for that, about timestamps:

TIMESTAMP values ​​are converted from the current time zone for UTC to store and convert from UTC to the current time zone for search. (This only happens for the TIMESTAMP data type, not others such as DATETIME.) By default, the current time zone for each connection is the server time. The time zone can be set for each connection, as described in Section 9.6, “MySQL Server Time Zone Support.” As long as the time zone setting remains constant, you return the same value that you store. If you store the TIMESTAMP value, and then change the time zone and get the value, the extracted value is different from the value that you saved.This is because the same time zone was not used to convert in both directions.The current time zone is available as the system time_zone belt.

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 will translate the timestamp into the correct time zone. There is no need to do anything complicated in Java except to set the time zone (I think it might even be a parameter in the JDBC connection string)

Answer 3

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