JDBC在UTC中给MySQL日期时间 [英] JDBC give MySQL datetime in UTC

问题描述

我的Java:

Date parsedDate = dateFormat.parse(timestamp);
Timestamp dbTimestamp = new Timestamp(parsedDate.getTime());       
insertTimestampPreparedStatement.setTimestamp(3, dbTimestamp);

PhpMyAdmin/MySQL始终在我的本地时间显示此内容，即使我这样做

insertTimestampPreparedStatement.setTimestamp(3, dbTimestamp, Calendar.instance(TimeZone.getTimeZone("UTC"));

我不确定MySQL是否将它们存储为时区信息，而我检查的唯一方法是直接在phpmyadmin中将时区行更新为NOW()，然后在GMT中显示了一段时间.因此，要么phpmyadmin/mysql中存在错误，要么我的代码未在正确的时区中通过时间戳发送.

如何在UTC中使用它?

解决方案

在获取或存储时间戳时，除非提供了具有特定时区的Calendar，否则JDBC驱动程序必须使用本地时区. >

不幸的是，关于驱动程序的行为，JDBC规范并不总是很清楚，而且看起来MySQL做错了.对于MySQL，您可以通过在连接字符串serverTimeZone和相关属性中指定一个或多个选项来强制使用它的时区.参见 http://dev.mysql. com/doc/connector-j/en/connector-j-reference-configuration-properties.html 获取完整列表.

My Java:

Date parsedDate = dateFormat.parse(timestamp);
Timestamp dbTimestamp = new Timestamp(parsedDate.getTime());       
insertTimestampPreparedStatement.setTimestamp(3, dbTimestamp);

PhpMyAdmin/MySQL always displays this in my local time, even when I do

insertTimestampPreparedStatement.setTimestamp(3, dbTimestamp, Calendar.instance(TimeZone.getTimeZone("UTC"));

I'm not sure if MySQL stores them aware of timezone, and the only way I checked this was updating a timezone row to NOW() directly in phpmyadmin, and it then showed me a time in GMT. So either there is a bug in phpmyadmin/mysql or my code is not sending over timestamps in the correct timezone.

How do I get this to work in UTC?

解决方案

A JDBC driver must use the local timezone when retrieving or storing timestamps, unless a Calendar with the specific timezone is provided.

Unfortunately, the JDBC spec isn't always clear on how drivers should behave, and it looks like MySQL does it wrong. For MySQL you can force the timezone it uses by specifying one or more options in the connection string serverTimeZone and related properties. See http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html for a full list.

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