java - 使用 Java 在 PostgreSQL 中存储时间的最推荐方法是什么？

Question

我在 PostgreSQL 数据库中存储了两个日期。第一个是网页的访问数据，第二个日期是网页最后一次修改的日期(这个是get as long)。

我有些怀疑存储这些值的最佳策略是什么。

我只需要日/月/年和小时:秒，这仅用于统计建议。

所以，有些疑问:

• 最好存储时间长并在恢复信息时进行转换，还是以上述数据格式存储？
• 最好是在软件上设置访问日期还是在数据库中插入？
• 在 Java 中，处理日期的最佳类是什么？

Answer 1

任何在 PostgreSQL 中存储日期和时间数据的策略，IMO，都应该依赖于这两点:

• 您的解决方案应该从不依赖于服务器或客户端的时区设置。
• 目前，PostgreSQL(与大多数数据库一样)没有数据类型来存储带有时区的完整日期和时间。因此，您需要在 Instant 之间做出决定或 LocalDateTime数据类型。

我的食谱如下。

---

如果您想记录特定事件发生时的物理瞬间(真正的“时间戳”，通常是一些创建/修改/删除事件)，那么使用:

• Java:Instant (Java 8 或 Jodatime)。
• JDBC:java.sql.Timestamp
• PostgreSQL:TIMESTAMP WITH TIMEZONE (TIMESTAMPTZ)

(不要让 PostgreSQL 特殊的数据类型 WITH TIMEZONE/WITHOUT TIMEZONE 迷惑你:它们实际上都没有存储时区)

一些样板代码:以下假设 ps 是 PreparedStatement，rs 是 ResultSet 和 tzUTC 是一个静态 Calendar 对象，对应于 UTC 时区。

public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));  

将Instant写入数据库TIMESTAMPTZ:

Instant instant = ...;
Timestamp ts = instant != null ? Timestamp.from(instant) : null;
ps.setTimestamp(col, ts, tzUTC);   // column is TIMESTAMPTZ!

从数据库TIMESTAMPTZ中读取Instant:

Timestamp ts = rs.getTimestamp(col,tzUTC); // column is TIMESTAMPTZ
Instant inst = ts !=null ? ts.toInstant() : null;

如果您的 PG 类型是 TIMESTAMPTZ，这将安全工作(在这种情况下，calendarUTC 在该代码中无效；但始终建议不要依赖默认时区)。“安全”意味着结果将不依赖于服务器或数据库时区，或时区信息:操作是完全可逆的，无论时区设置发生什么，您将始终获得相同的“即时时间”，你最初在 Java 端有。

---

如果您处理的是“公民”本地日期时间(即字段集{year -month-day hour:min:sec(:msecs)})，你会使用:

• Java:LocalDateTime (Java 8 或 Jodatime)。
• JDBC:java.sql.Timestamp
• PostgreSQL:TIMESTAMP WITHOUT TIMEZONE (TIMESTAMP)

从数据库TIMESTAMP中读取LocalDateTime:

Timestamp ts = rs.getTimestamp(col, tzUTC); //
LocalDateTime localDt = null;
if( ts != null ) 
    localDt =  LocalDateTime.ofInstant(Instant.ofEpochMilli(ts.getTime()), ZoneOffset.UTC);

将LocalDateTime写入数据库TIMESTAMP:

  Timestamp ts = null;
  if( localDt != null)    
      ts = new Timestamp(localDt.toInstant(ZoneOffset.UTC).toEpochMilli()), tzUTC);
  ps.setTimestamp(colNum,ts, tzUTC); 

同样，这种策略是安全的，您可以安然休眠:如果您存储了 2011-10-30 23:59:30 ，您将检索到那些精确的字段(小时=23，分钟= 59...等)，无论如何 - 即使明天您的 Postgresql 服务器(或客户端)的时区发生变化，或者您的 JVM 或您的操作系统时区，或者您的国家/地区修改其 DST 规则等。

---

添加:如果您想要(这似乎是一个自然的要求)存储完整的日期时间规范(ZonedDatetime:时间戳和时区，其中隐含地还包括完整的民用日期时间信息 - 加上时区).. . 那么我有个坏消息要告诉你:PostgreSQL 没有这种数据类型(据我所知，其他数据库也没有)。您必须设计自己的存储，可能在一对字段中:可能是上述两种类型(高度冗余，但对于检索和计算很有效)，或者其中一种加上时间偏移(您丢失时区信息，一些计算变成困难，有些不可能)，或者其中之一加上时区(作为字符串；一些计算可能非常昂贵)。