如何将java.util.Date存储到UTC / GMT时区的MySQL时间戳字段中？ [英] How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?
问题描述
我使用了一个新的Date（）对象来填充MySQL数据库中的字段，但是该字段中存储的实际值是我当地的时区。
I used a new Date() object to fill a field in a MySQL DB, but the actual value stored in that field is in my local timezone.
我可以配置MySQL将其存储在UTC / GMT时区吗？
How can I configure MySQL to store it in the UTC/GMT timezone?
我认为，配置连接字符串会有所帮助，但我不知道如何。连接字符串中有许多属性，如useTimezone，serverTimzone，useGmtMillisForDatetimes，useLegacyDatetimeCode，...
I think, configuring the connection string will help but I don't know how. There are many properties in the connection string like useTimezone, serverTimzone, useGmtMillisForDatetimes, useLegacyDatetimeCode, ...
推荐答案
简短的回答是：
- 在代码中添加default-time-zone = utc到my.cnf
- ，总是在UTC中思考，除非在显示用户的日期时
-
在使用JDBC获取/设置日期或时间戳时，请始终使用Calendar参数，设置为UTC：
- add "default-time-zone=utc" to my.cnf
- in your code, always "think" in UTC, except when displaying dates for your users
when getting/setting dates or timestamps with JDBC, always use the Calendar parameter, set to UTC:
resultset.getTimestamp（my_date，Calendar.getInstance（TimeZone.getTimeZone（UTC）））;
resultset.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));
答案很长：
在处理任何数据库中的日期和时区以及任何客户端代码时，我通常建议采用以下政策：
When dealing with dates and timezones in any database and with any client code, I usually recommend the following policy:
-
配置数据库以使用U TC时区，而不是使用服务器的本地时区（当然，除非它是UTC）。
Configure your database to use UTC timezone, instead of using the server's local timezone (unless it is UTC of course).
-
如何这样做取决于您的数据库服务器。可以在此处找到有关MySQL的说明： http：// dev .mysql.com / DOC / refman / 5.0 / EN /时区-的support.html 。基本上你需要在my.cnf中写这个：default-time-zone = utc
How to do so depends on your database server. Instructions for MySQL can be found here: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html. Basically you need to write this in my.cnf: default-time-zone=utc
这样你可以在任何地方托管你的数据库服务器，改变你的托管位置很容易，更普遍地操作服务器上的日期而没有任何歧义。
This way you can host your database servers anywhere, change your hosting location easily, and more generally manipulate dates on your servers without any ambiguity.
- 例如，如果您正在构建电话服务并且在数据库服务器上使用夏令时，那么您就会遇到麻烦：那里无法判断从2008-10-26 02:30:00到2008-10-26 02:35:00的客户是否实际呼叫5分钟或1小时5分钟（假设夏令时发生在10月26日凌晨3点）！
- For example, if you are building a telephony service and you are using Daylight Saving Time on your database server then you are asking for trouble: there will be no way to tell whether a customer who called from "2008-10-26 02:30:00" to "2008-10-26 02:35:00" actually called for 5 minutes or for 1 hour and 5 minutes (supposing Daylight Saving occurred on Oct. 26th at 3am)!
在应用程序代码中，除了向用户显示日期外，始终使用UTC日期。
- 在Java中，从数据库中读取时，请始终使用：
时间戳myDate = resultSet.getTimestamp（my_date，Calendar.getInstance （TimeZone.getTimeZone（UTC）））;
Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));
- 如果你不这样做，时间戳将被假定在你的本地TimeZone，而不是UTC。
同步您的服务器或仅依赖于数据库服务器的时间
-
如果您的Web服务器在一台服务器（或更多）上，而您的数据库服务器在其他服务器上，我强烈建议你将它们的时钟与NTP同步。
If you have your Web server on one server (or more) and your database server on some other server, then I strongly recommend you synchronize their clocks with NTP.
或者，只依靠一台服务器告诉你它的时间。通常，数据库服务器是最好的时间。换句话说，避免使用如下代码：
OR, only rely on one server to tell you what time it is. Usually, the database server is the best one to ask for time. In other words, avoid code such as this:
preparedStatement = connection.prepareStatement（UPDATE my_table SET my_time =？ WHERE [...]）;
java.util.Date now = new java.util.Date（）; // 当地时间！ :-(
preparedStatement.setTimestamp（1，new Timestamp（now.getTime（）））;
int result = preparedStatement.execute（）;
preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = ? WHERE [...]");
java.util.Date now = new java.util.Date(); // local time! :-(
preparedStatement.setTimestamp(1, new Timestamp(now.getTime()));
int result = preparedStatement.execute();
- 相反，依赖数据库服务器的时间：
preparedStatement = connection .prepareStatement（UPDATE my_table SET my_time = NOW（）WHERE [...]）;
int result = preparedStatement.execute（）;
preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = NOW() WHERE [...]");
int result = preparedStatement.execute();
希望这会有所帮助！： - ）
Hope this helps! :-)
这篇关于如何将java.util.Date存储到UTC / GMT时区的MySQL时间戳字段中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
