JDBC/MySQL:始终使用UTC保存时间戳记 [英] JDBC/MySQL: Save timestamp always using UTC

问题描述

我想将时间戳记保存到数据库，而不必通过jdbc驱动程序将其转换为本地时区.目前，只有MySQL和PostgreSQL对我来说很重要，但是如果有独立于数据库的解决方案，我将不胜感激.

I would like to save a timestamp to the database without being converted to the local timezone by the jdbc driver. Currently only MySQL and PostgreSQL are important for me but i would appreciate if there is a database independent solution.

示例:

// i want that to be saved as 1970-01-01 00:00:00
TimeStamp ts = new java.sql.Timestamp(0);

// gets transformed to local time by jdbc driver (in my case to 1970-01-01 01:00:00)
st.setTimestamp(0, new java.sql.Timestamp(0));

// only works using postgres (mysql connector seems to ignore the calendar)
// postgres => 1970-01-01 00:00:00
// mysql => 1970-01-01 01:00:0
Calendar calutc = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
st.setTimestamp(0, new java.sql.Timestamp(0), utccal);

我已经尝试计算时间戳值，将其转换为正确的值(例如，在我的时区中为-3600000 => 1970-01-01 00:00:00)，但这不适用于日期上的postgres接近日光节约时间.

I already tried to calculate a timestamp value that get transformed to the correct value (for example -3600000 => 1970-01-01 00:00:00 in my time zone) but this doesn't work on postgres on dates near the day light saving time changes.

推荐答案

我找到了解决方案. MySQL在其JDBC连接器中存在一个错误，忽略了提供给setTimestamp/getTimestamp的Calendar对象.他们修复了连接器版本5.1.5中的错误，但旧的(错误的)行为仍然是默认行为.要使用正确的代码，您必须将参数"useLegacyDatetimeCode = false"传递给连接器URL.

I found the solution. MySQL had a bug in their JDBC connector ignoring the provided Calendar object to setTimestamp/getTimestamp. They fixed the bug in version 5.1.5 of the connector but the old (incorrect) behaviour is still the default behaviour. To use the correct code you have to pass the parameter "useLegacyDatetimeCode=false" to the connector url.

其他信息: http://bugs.mysql.com/bug.php?id=15604

这篇关于JDBC/MySQL:始终使用UTC保存时间戳记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！