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

Question

除了显示时刻，我希望所有时间都以协调世界时（UTC）表示。在最后一刻，它们可以转换为本地时间仅用于显示。

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

我如何从数据库检索和发送时间对象到UTC，以便数据库不进行任何转换？

---

尽管是针对PostgreSQL编写的，但我试图遵循Basil Bourque在下面问题上的出色答案。我使用Instant几乎在所有地方，但如他所示，在与JDBC交互时，我使用OffsetDateTime，因为它是唯一受JDBC 4.2支持的现代时刻类。

• 如何在Java 8中将UTC日期转换为UTC OffsetDateTime？
• 在保存SQL Server中的当前日期和UTC时间时存在问题
• 和在使用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呢？

- user1902689

Answer 1

TIMESTAMP WITHOUT TIME ZONE

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

java.time.LocalDateTime

所以在从那个列检索值时，您正在使用错误的Java类。不要使用OffsetDateTime，而是使用LocalDateTime。像（和）一样，Java类型表示具有日期和时间但没有区域或偏移量的日期。

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

确定一个时刻

您可以为时刻指定偏移量或时区，以确定时间轴上的某个点。

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

或者：

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

---

你说：

它会分配“-04:00”的系统或服务器（同一台机器）偏移量，从而搞乱一切。

在遍历的过程中，某些部分试图帮助您的代码意图以仅包含日期和时间值开头，最终分配了一个偏移量。很可能是您的JDBC驱动程序分配了一个偏移量。由于没有在MySQL DATETIME 中存储偏移量，因此需要分配一些偏移量才能生成所请求的OffsetDateTime对象。

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

你问道：

我如何检索并发送UTC时间对象与数据库一起工作，以便数据库不进行任何转换？

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

您无法将以UTC为基础看到的时刻与缺少UTC偏移概念的数据库列交换。

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

由于你似乎在当前工作情况中陷入了困境，你可以 假装 你的 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 ) ;

这种伪装的方法充满了危险和不负责任。数据库正在追踪虚构，而不是真相。一定要记录这个黑客攻击，因为任何新接手该系统的程序员都会感到彻底困惑。

顺便说一下，注意类型名称TIMESTAMP WITH TIME ZONE和TIMESTAMP WITHOUT TIME ZONE是错误的。据我所知，并从草案中看到，SQL标准实际上只意味着偏移量，而不是真正的时区。这就是为什么OffsetDateTime是唯一映射到TIMESTAMP WITH TIME ZONE的Java类。

- Basil Bourque