JDBC/MySQL:始终使用 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 保存时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！