如何以 UTC 和本地时区存储日期时间
存储日期时间的实用方法是什么，以便我可以让用户查看/查询自己当地时间的数据，同时保留有关原始日期时间的信息。
基本上，用户希望能够查询（截至其当地时间）从不同时区的系统收集的数据。但有时，他们想知道数据是在原始系统中于 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*
我正在寻找有关如何存储数据的建议。
我正在考虑将所有日期时间存储在 UTC 中，并添加一个包含原始时区名称的附加列，其形式允许我使用 mysql CONVERT_TZ 或 Java 中的对应项。然后，应用程序会将用户输入的日期转换为 UTC，这样我就可以轻松查询数据库。所有日期也可以在应用程序中轻松转换为用户当地时间。使用原始时区列，我还可以显示原始日期时间。
然而，这意味着对于我拥有的每个日期时间，我都需要一个额外的列......
start_time_utc datetime
start_time_tz varchar(64)
end_time_utc datetime
end_time_tz varchar(64)
我在这里的轨道正确吗？
有使用过此类数据的人可以分享他们的经验吗？
（我将使用 MySQL 5.5 CE）
更新 1
数据将以 xml 文件形式提供，其中每个条目都有某个本地时区的日期时间。所以只会有一个插入进程，在一处运行。
一旦加载到数据库中，它将在某些 Web 应用程序中呈现给不同时区的用户。对于大多数用例，感兴趣的数据也来自与查看数据的用户相同的时区。对于一些更复杂的用例，一系列事件相互关联并跨越多个时区。因此，用户希望能够谈论这些事件，以便调查对方当时可能的原因/后果。不是 UTC，也不是他们自己的当地时间。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
由于用户可以居住在不同的时区，甚至可以从一个时区移动到另一个时区，因此最佳实践是以 UTC 格式存储日期和时间，并在显示时转换为用户的时区。
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.
该手册有一个专门针对此目的的部分，关于时间戳：
所以你可以在客户端使用：
SET time_zone = timezone;来设置时区。然后所有查询都会将时间戳转换为正确的时区。除了设置时区（我认为甚至可能是 JDBC 连接字符串中的参数）之外，不需要在 Java 中做任何复杂的事情The manual has a section just for this, about timestamp:
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)您始终可以将祖鲁时间作为所有计算的基础。
You can always get the zulu time as base for all your calculations.