存储Joda LocalDateTime在PostgreSQL的'timestamp'列中存在DST问题

Question

我们的应用程序存储属于许多不同时区的日期时间。 我们决定使用Joda LocalDateTime类型-这样用户始终可以得到他们最初输入的内容。 这正是我们需要的。

在内部，我们知道用户属于哪个时区-因此当他们输入日期时间时，我们执行以下检查：

dateTimeZone.isLocalDateTimeGap(localDateTime)

如果该日期时间在用户所在时区不存在（处于夏令时间隙），我们会显示错误消息，指出日期不正确，从而防止将不正确的日期时间存储在数据库中。

我们使用一个时间戳列进行存储。问题在于，当用户输入的日期时间存在于其时区中但不存在于数据库时区（欧洲/柏林）时，就会出现问题。例如，当我从欧洲/伦敦时区存储本地日期时间2015-03-29 02:30:00（这是有效的——在伦敦，间隔在01:00和02:00之间）时，PostgreSQL会将小时数向前推移1小时，并将其保存为2015-03-29 03:30:00。

怎么办？有没有办法告诉PostgreSQL不要涉及任何时区，只是按照Joda所表示的字面意思存储日期时间？（除了将它们存储为字符串之外；）

- machinery

---

在Postgres中，“timestamp”不具备时区感知能力。因此，Postgres不会改变时间。您能否发布保存值到数据库的代码？另外，您正在运行哪个版本的Postgres？ - Matt Johnson-Pint

请确保运行PostgreSQL的服务器使用GMT+00:00作为固定偏移量，以避免本地时间戳相关的操纵。此外，您的jdbc驱动程序是否对本地时区敏感？如果是，则可能有一些配置选项可用于使用固定偏移量。 - Meno Hochschild

@MattJohnson和Meno Hochschild - 感谢你们的评论 :) Matt的评论“时间戳不具有时区意识”帮助我解决了问题。显然是由于我的编辑器（SQLWorkbench）导致了时间戳的偏移。当我在pgAdmin / psql中显示时间戳时，它没有被偏移。如果Matt将此评论作为答案添加，我会接受它。 - machinery

为了清晰明确，描述Postgres或任何其他符合SQL标准的数据库中的TIMESTAMP WITHOUT TIME ZONE或TIMESTAMP WITH TIME ZONE列类型时，请使用完整的正式名称。它们的含义和行为是完全不同的。 - Basil Bourque

Answer 1

在PostgreSQL 7.3及以上版本中，timestamp等同于timestamp without time zone。该数据类型不考虑时区，仅存储日期和时间。如果您发现时间有偏移，可能与您用于存储或检索数据的代码或工具有关。

请注意，在版本7.3之前，timestamp等同于timestamp with timezone。这在文档第一个注释框中提到了。

- Matt Johnson-Pint

Answer 2

Postgres根据SQL标准提供了两种日期时间类型。不幸的是，标准对此话题只是简单地涉及了一下，因此这里描述的行为是特定于Postgres的。其他数据库可能会有不同的行为。

• TIMESTAMP WITHOUT TIME ZONE
  仅存储日期和时间。任何传递的时区或UTC偏移量都将被忽略。
• TIMESTAMP WITH TIME ZONE
  首先使用传递的区域/偏移量调整传递的日期+时间以获取UTC值。在进行调整后，传递的区域/偏移量将被丢弃；如果需要，您必须自己将原始的区域/偏移量信息存储在单独的列中。

请注意，TIMESTAMP WITHOUT TIME ZONE并不代表实际时刻，也不存储时间轴上的点。如果没有区域或偏移量的上下文，它就没有真正的意义。它代表了大约26-27小时内可能发生的一系列时刻。适用于存储未来足够遥远的约会问题，因为在其到达之前时区规则可能会更改。也适用于“圣诞节在今年12月25日午夜后开始”，其中每个时区都表示不同的时间点，每个时区向西逐渐到达的时间越来越晚。

记录实际时刻、时间轴上的特定点时，请使用TIMESTAMP WITH TIME ZONE。

Java中的现代方法使用java.time类，而不是Joda-Time库或早期版本Java捆绑的麻烦的旧日期时间类。

TIMESTAMP WITHOUT TIME ZONE

对于TIMESTAMP WITHOUT TIME ZONE，java.time中的等效类是LocalDateTime，用于表示没有任何偏移或区域的日期和时间。

正如其他人所指出的那样，一些工具可能会在错误但出于好意的反特性下动态应用时区到检索到的值中，这会导致混淆。以下Java代码将检索您真实的日期时间值，不包括时区/偏移量。

需要符合JDBC 4.2或更高版本的JDBC驱动程序才能直接使用java.time类型。

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;  // Retrieving a `TIMESTAMP WITHOUT TIME ZONE` value.

插入/更新数据库：

myPreparedStatement.setObject( … , ldt ) ;  // Inserting/updating a `TIMESTAMP WITHOUT TIME ZONE` column.

带时区的时间戳

你讨论的时区涉及到时间轴上的实际时刻，所以你应该绝对使用带时区的时间戳(TIMESTAMP WITH TIME ZONE)而不是不带时区的时间戳(TIMESTAMP WITHOUT TIME ZONE)。你不应该去操纵夏令时(Daylight Saving Time, DST)的差异等问题。让Java.time和Postgres为你完成这项工作，用已经编写并测试过的更好的代码。

取回：

Instant instant = myResultSet.getObject( … , Instant.class ) ;  // Retrieving a `TIMESTAMP WITH TIME ZONE` value in UTC.
ZonedDateTime zdt = instant.atZone( ZoneId.of( "Africa/Tunis" ) ) ;  // Adjusting from a UTC value to a specific time zone.

插入/更新数据库：

myPreparedStatement.setObject( … , zdt ) ;  // Inserting/updating a `TIMESTAMP WITH TIME ZONE` column.

从数据库中检索：

Instant instant = myResultSet.getObject( … , Instant.class ) ;

不，不，不。不要这样操作。您在Java和Postgres的类型上滥用了。如果用户输入2015-03-29 02:30:00以表示Europe/London时区中的某一时刻，则应将其解析为LocalDateTime并立即应用ZoneId以获取ZonedDateTime。

要进行解析，请使用T替换中间的空格，以符合java.time类中默认使用的ISO 8601标准格式。

String input = "2015-03-29 02:30:00".replace( " " , "T" ) ;
LocalDateTime ldt = LocalDateTime.parse( input ) ;
ZoneId z = ZoneId.of( "Europe/London" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ; 

要查看相同的UTC时刻，请提取一个Instant。Instant类表示时间线上以UTC为基准的瞬间，分辨率为纳秒（最多九位小数）。

Instant instant = zdt.toInstant() ;

通过 JDBC 将时间戳存储到数据库中，使用 TIMESTAMP WITH TIME ZONE 类型。

myPreparedStatement.setObject( … , instant ) ;

使用对象，而非字符串

请注意，我在这里的所有代码都使用java.time对象与数据库交换数据。在交换日期时间值时，请始终使用这些对象，而不仅仅是字符串。

---

关于java.time

java.time框架内置于Java 8及以上版本中。这些类取代了老旧的遗留日期时间类，如java.util.Date、Calendar和SimpleDateFormat。

Joda-Time项目现在处于维护模式，建议迁移到java.time类。

想要了解更多，请查看Oracle教程。并在Stack Overflow上搜索许多示例和解释。规范是JSR 310。

如何获取java.time类？

• Java SE 8，Java SE 9以及之后的版本
  • 内置。
  • 作为标准Java API的一部分，附带实现。
  • Java 9增加了一些小功能和修复。
• Java SE 6和Java SE 7
  • 大部分java.time功能在ThreeTen-Backport中被移植到Java 6和7中。
• Android
  • ThreeTenABP项目专门为Android适配ThreeTen-Backport（如上所述）。
  • 请参阅{{link8：如何使用ThreeTenABP…}}。

ThreeTen-Extra 项目通过添加额外的类扩展了 java.time。该项目是 java.time 可能未来增加内容的试验场。您可能会在这里找到一些有用的类，例如 Interval, YearWeek, YearQuarter 和 更多。

- Basil Bourque