Java，Hibernate，MySQL - 存储UTC日期时间 [英] Java, Hibernate, MySQL - store UTC date-time

问题描述

我在这里看到了一些相关的问题（如这一个，当然，这一个）...基本上，我想要的是将日期时间作为UTC存储，并让应用程序用户选择他希望显示日期时间的时区。

由于似乎日期时间字段受底层JDBC驱动程序的影响，我想知道这是否可以接受存储UTC日期时间的方法：

I saw here on SO a few related questions (like this one and of course, this one)... Essentially, what I want is to store date-time as UTC, and let application user choose the time zone he wants to display date-time in.
Since it seems that date-time fields are affected by the underlying JDBC driver, I wonder if this is an acceptable way to go about storing UTC date-time:

• 将MySQL和应用程序服务器机器设置为UTC时区（不需要分开）


• MySQL和JVM都应该接收底层系统时间设置（如果没有指示）


• 在MySQL端使用 DATETIME 表列


• 使用 java.util.Date 作为Hibernate的对应映射ide（我猜 java.sql.Timestamp 也可以使用）


• 让应用程序担心解释日期时间字段 - 即让用户选择首选时区

• Set both MySQL and Application server machine to UTC time zone (no need to separate)
• Both MySQL and JVM should pick up underlying system time settings (if not instructed otherwise)
• Use DATETIME table columns on MySQL side
• Use java.util.Date as corresponding mapping on Hibernate side (I guess java.sql.Timestamp could be used too)
• Let the application worry about interpreting date-time fields - i.e. let the user choose preferred time zone

这样可以吗？

EDIT

要澄清 - 这里我的意思是指严格在服务器上创建的时间戳（egdate-record of record creation）。因此，应用程序服务器实例化 Date 对象（新的Date（）等于服务器上的当前日期时间，这是真正的时区不可知的）

现在如果客户端用户想要提供一些搜索/过滤的日期，那么这里是从客户端到本地时间到UTC的转换，IMHO ...

EDIT
To clarify - here I meant to refer to timestamps created strictly on the server (e.g.date-time of record creation). So the application server instantiates Date objects (new Date() equals current date-time on the server, and this is really time zone agnostic).
Now if a client user wants to supply some date for searching/filtering purposes, here is where the transformation from client-local time to UTC should take place, IMHO...

推荐答案

我会建议一个简单的方法，它将独立于机器时区设置。

I would suggest another simple approach which would independent of machine timezone settings.

• 设置服务器机器的时区，而不是设置JVM的时区。这可以通过系统属性完成。在Windows上的例子如下

set JAVA_OPTS=%JAVA_OPTS% -Duser.timezone=GMT

• 对于MySQL 这里是参考做这个


• 现在这样做确保您的所有日期时间都在GMT


• 将时区保留为可配置属性或者它也可以是用户依赖的。因此，如果用户属于不同的地理位置，则为每个用户存储时区。


• 每当需要日期时，从数据库中选择日期后，应用时区获取正确的时间。


• 方法是这将为所有的时区用户工作。意思是用户将根据他们的时区看到正确的时间。


• For MySQL here is the reference for doing it.
• Now this this make sure that all your date-time are in GMT
• Keep the timezone as configurable property OR it can be user dependent as well. So you store timezone for each user if the users belong to different geographies.
• Whenever, a date is needed, after you select it from the database, apply the timezone to get the correct time.
• The advantage of this approach is that this will work for the all the timezone users. Meaning the user will see the correct time as per their timezone.

这篇关于Java，Hibernate，MySQL - 存储UTC日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！