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.
基本上,用户希望能够查询(从他们自己的本地时间)从各个时区的系统收集的数据。但偶尔,他们想知道数据是在原始系统中的18:00创建的。当来自世界不同地区的用户就同一事件进行通信时,它会有所帮助。
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.
我正在考虑以UTC格式存储所有日期时间并添加包含原始时区名称的附加列,其形式允许我使用mysql CONVERT_TZ或Java中的对应项。然后,应用程序将用户输入的日期转换为UTC,我可以轻松查询数据库。所有日期也可以轻松转换为应用程序中的用户本地时间。使用原始时区列我也可以显示原始日期时间。
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)
(我将使用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.
数据将以xml文件的形式提供,其中每个条目在某个本地时区具有日期时间。因此,只有一个插入过程,在一个地方运行。
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.
一旦加载到数据库中,它将在一些Web应用程序中呈现给不同时区的用户。对于大多数用例,感兴趣的数据也来自与查看数据的用户相同的时区。对于一些更复杂的用例,一系列事件是互连的并跨越多个时区。因此,用户希望能够谈论事件,以便在另一个时间调查可能的原因/后果。不是UTC,不是他们当地的时间。
3 个解决方案
#1
5
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.
由于用户可以居住在不同的时区,甚至可以从一个时区移动到另一个时区,最佳做法是以UTC格式存储日期和时间,并在显示时转换为用户的时区。
#2
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.
TIMESTAMP值从当前时区转换为UTC以进行存储,并从UTC转换回当前时区以进行检索。 (这仅适用于TIMESTAMP数据类型,而不适用于其他类型,例如DATETIME。)默认情况下,每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区,如第9.6节“MySQL服务器时区支持”中所述。只要时区设置保持不变,您就可以获得存储的相同值。如果存储TIMESTAMP值,然后更改时区并检索该值,则检索的值与您存储的值不同。发生这种情况是因为在两个方向上都没有使用相同的时区进行转换。当前时区可用作time_zone系统变量的值。
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)
所以你可以使用:SET time_zone = timezone;在客户端上设置时区。然后,所有查询都会将时间戳转换为正确的时区。除了设置时区(我想甚至可能是JDBC连接字符串中的参数)之外,无需在Java中执行任何复杂的操作
#3
0
You can always get the zulu time as base for all your calculations.
您始终可以将zulu时间作为所有计算的基础。
#1
5
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.
由于用户可以居住在不同的时区,甚至可以从一个时区移动到另一个时区,最佳做法是以UTC格式存储日期和时间,并在显示时转换为用户的时区。
#2
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.
TIMESTAMP值从当前时区转换为UTC以进行存储,并从UTC转换回当前时区以进行检索。 (这仅适用于TIMESTAMP数据类型,而不适用于其他类型,例如DATETIME。)默认情况下,每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区,如第9.6节“MySQL服务器时区支持”中所述。只要时区设置保持不变,您就可以获得存储的相同值。如果存储TIMESTAMP值,然后更改时区并检索该值,则检索的值与您存储的值不同。发生这种情况是因为在两个方向上都没有使用相同的时区进行转换。当前时区可用作time_zone系统变量的值。
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)
所以你可以使用:SET time_zone = timezone;在客户端上设置时区。然后,所有查询都会将时间戳转换为正确的时区。除了设置时区(我想甚至可能是JDBC连接字符串中的参数)之外,无需在Java中执行任何复杂的操作
#3
0
You can always get the zulu time as base for all your calculations.
您始终可以将zulu时间作为所有计算的基础。