使用 Java 在 PostgreSQL 中存储时间的最推荐方法是什么? [英] What is the most recommended way to store time in PostgreSQL using Java?

问题描述

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

I'm storing two dates in the PostgreSQL database. First, is the data of visit of a webpage, and the second date is the date of last modification of the webpage(this is get as a long).

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

I have some doubts what is the best strategy to store these values.

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

I only need day/month/year and hour:seconds and this will only for statistical proposes.

所以，有些疑问:

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

推荐答案

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

Any strategy for storing date-and-time data in PostgreSQL should, IMO, rely on these two points:

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

• Your solution should never depend on the server or client timezone setting.
• Currently, PostgreSQL (as most databases) doesn't have a datatype to store a full date-and-time with timezone. So, you need to decide between an Instant or a LocalDateTime datatype.

我的食谱如下.

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

If you want to record the physical instant at when a particular event ocurred, (a true "timestamp" , typically some creation/modification/deletion event), then use:

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

(不要让 PostgreSQL 特有的数据类型 WITH TIMEZONE/WITHOUT TIMEZONE 混淆你:它们中没有一个实际存储时区)

(Don't let PostgreSQL peculiar datatypes WITH TIMEZONE/WITHOUT TIMEZONE confuse you: none of them actually stores a timezone)

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

Some boilerplate code: the following assumes that ps is a PreparedStatement, rs a ResultSet and tzUTC is a static Calendar object corresponding to UTC timezone.

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 方面.

This works safely if your PG type is TIMESTAMPTZ (In that case, the calendarUTC has no effect in that code ; but it's always advisable to not depend on defaults timezones). "Safely" means that the result will not depend on server or database timezone, or timezones information: the operation is fully reversible, and whatever happens to timezones settings, you'll always get the same "instant of time" you originally had on the Java side.

如果，而不是时间戳(物理时间线上的一个瞬间)，您正在处理一个公民"本地日期时间(即字段集{year-month-day hour:min:sec(:msecs)})，您将使用:

If, instead of a timestamp (an instant on the physical timeline), you are dealing with a "civil" local date-time (that is, the set of fields {year-month-day hour:min:sec(:msecs)}), you'd use:

• 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 规则等.

Again, this strategy is safe and you can sleep peacefully: if you stored 2011-10-30 23:59:30 , you'll retrieve those precise fields (hour=23, minute=59... etc) always, no matter what - even if tomorrow the timezone of your Postgresql server (or client) changes, or your JVM or your OS timezone, or if your country modifies its DST rules, etc.

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

Added: If you want (it seems a natural requirement) to store the full datetime specification (a ZonedDatetime: the timestamp together with the timezone, which implicitly also includes the full civil datetime info - plus the timezone)... then I have bad news for you: PostgreSQL hasn't a datatype for this (neither other databases, to my knowledge). You must devise your own storage, perhaps in a pair of fields: could be the two above types (highly redundant, though efficient for retrieval and calculation), or one of them plus the time offset (you lose the timezone info, some calculations become difficult, and some impossible), or one of them plus the timezone (as string; some calculations can be extremely costly).

这篇关于使用 Java 在 PostgreSQL 中存储时间的最推荐方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！