java - 如何使用 MySQL Connector/J 直接检索 UTC OffsetDateTime？

Question

除了显示时间外，我更喜欢所有时间都使用 UTC。在最后一刻，它们可以转换为本地时间仅供显示。

我之前一直在 MySQL 中的一个整数字段中存储 UNIX 时间戳(自纪元以来的秒数)。我正在使用我无法更改的其他人的数据库架构，它使用 DATETIME 存储原始时间，甚至没有偏移量。

如何使用 UTC 数据库检索和发送时间对象，使数据库完全不进行任何转换？

---

虽然是为 PostgreSQL 编写的，但我尝试遵循 Basil Bourque 对以下问题的出色回答。我在任何地方都使用 Instant，除了他展示我在与 JDBC 交互时使用 OffsetDateTime，因为它是 JDBC 4.2 保证支持的唯一现代时刻类。

• How to convert UTC date to UTC OffsetDateTime in java 8?
• Issue in saving current date with UTC time in sql server
• 和How to save correct date time with timezone when using jdbc

我正在使用 MySQL Connector/J 驱动程序版本 8.0.27，并且我正在使用 &preserveInstants=false&connectionTimeZone=UTC 进行连接。

让我们看一下数据库中存储为“2022-01-14 11:00:00”的 DATETIME，它被理解为 UTC。

resultSet.getString(columnName); // "2022-01-14 11:00:00"
resultSet.getObject(columnName, OffsetDateTime.class).toString() // "2022-01-14T11:00-04:00"
resultSet.getObject(columnName, OffsetDateTime.class).toInstant().toString() // "2022-01-14T15:00:00Z"

当它创建 OffsetDateTime 时，它会分配“-04:00”的系统或服务器(同一台机器)偏移量并将一切搞砸。我认为我的连接选项 &preserveInstants=false&connectionTimeZone=UTC 应该已经停止了。

我知道之后可以使用 .withOffsetSameLocal(ZoneOffset.UTC).toInstant() 立即将偏移量设置为“+00:00”，但我如何避免这样做并直接使用它创建一个采用 UTC 的 OffsetDateTime？

Answer 1

没有时区的时间戳

您已经指出了核心问题:您的表的作者将该列定义为 MySQL DATETIME类型。该类型仅表示带有时间的日期，但缺少时区或偏移量的上下文。 MySQL DATETIME 列类似于 SQL 标准类型的 TIMESTAMP WITHOUT TIME ZONE。

java.time.LocalDateTime

因此，当您从 DATETIME 列中检索值时，您使用了错误的 Java 类。不要使用 OffsetDateTime，而是使用 LocalDateTime。与 DATETIME(和 TIMESTAMP WITHOUT TIME ZONE)一样，Java 类型 LocalDateTime 表示带有时间但没有时区或偏移量的日期。

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

确定时刻

您可以指定一个偏移量或时区来精确定位一个时刻，即时间轴上的一个点。

OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC ) ; 

或者:

ZoneId z = ZoneId.of( "America/Edmonton" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ;

---

你说:

it's assigning either the system or server (same machine) offset of "-04:00" and messing everything up.

遍历路线上的某些部分试图帮助您实现代码的意图，即以仅日期和时间的值开始，以分配的偏移量结束。您的 JDBC 驱动程序可能是分配偏移量的部分。由于 MySQL DATETIME 没有存储偏移量，因此需要分配一些 偏移量才能生成您请求的 OffsetDateTime 对象。

如上所示，更好的方法是在使用缺少偏移量的数据库列时不请求OffsetDateTime对象。

你问:

How can I retrieve and send time objects with the database in UTC, so the database is doing absolutely no conversions whatsoever?

您不能将在 UTC 中看到的时刻与缺少与 UTC 的偏移量概念的数据库列交换。

如果您想在 SQL 数据库中表示时刻，则必须使用适当的数据类型，一种类似于 SQL 标准类型 TIMESTAMP WITH TIME ZONE 的类型。

在将带有偏移量的日期时间值与仅包含日期时间的数据库列交换时，您要求的就像要求将日元价格存储在纯数字列中一样。您可以假装这些记录值代表以日元表示的价格，但您并不知道。有人也可以在那里以欧元或比索存储价格。

由于您似乎对当前的工作情况感到困惑，您可以假装您的 TIMESTAMP WITHOUT TIME ZONE 列存储 UTC 值。基本上，这就是我上面的代码示例所做的。

假装写入值，调整为 UTC，然后提取一个 LocalDateTime。

ZonedDateTime zdt = ZonedDateTime.now( z ) ;
OffsetDateTime odt = zdt.withOffsetSameInstant( ZoneOffset.UTC ) ; 
LocalDateTime ldt = odt.toLocalDateTime() ;
myPreparedStatement.setObject( … , ldt ) ;

这种假装的做法充满了危险，而且是不负责任的。数据库跟踪的是虚构的，而不是真相。请务必记录此 hack，因为任何刚接触该系统的程序员都会感到非常困惑。

顺便说一下，请注意类型名称 TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITHOUT TIME ZONE 是用词不当。据我所知，以及我在草稿中看到的情况，SQL 标准实际上只意味着偏移量，而不是真正的时区。这就是为什么 OffsetDateTime 是 JDBC 中唯一映射到 TIMESTAMP WITH TIME ZONE 的 Java 类。