使用 Java 在 PostgreSQL 中存储时间的最推荐方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6627289/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the most recommended way to store time in PostgreSQL using Java?
提问by Renato Dinhani
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).
我在 PostgreSQL 数据库中存储了两个日期。第一个是网页的访问数据，第二个日期是网页最后修改的日期（这个是长的）。
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.
我只需要日/月/年和小时：秒，这仅用于统计建议。
So, some doubts:
所以，有些疑惑：
- is best store as long and convert on recover of information or store in the data format above?
- is best set the date of visit on the software or in the insertion in the database?
- in Java, how are the best classes to handle dates?
- 最好存储尽可能长的时间并在恢复信息时转换还是以上述数据格式存储？
- 最好在软件上设置访问日期还是在数据库中插入？
- 在 Java 中，如何处理日期的最佳类？
采纳答案by leonbloy
Any strategy for storing date-and-time data in PostgreSQL should, IMO, rely on these two points:
IMO，任何在 PostgreSQL 中存储日期和时间数据的策略都应该依赖于以下两点：
- Your solution should neverdepend on the server or client timezone setting.
- Currently, PostgreSQL (as most databases) doesn't have a datatype to store a fulldate-and-time with timezone. So, you need to decide between an
Instantor aLocalDateTimedatatype.
- 您的解决方案应该永远依赖于服务器或客户端时区设置。
- 目前，PostgreSQL（与大多数数据库一样）没有数据类型来存储带有时区的完整日期和时间。因此，您需要在数据类型
Instant或LocalDateTime数据类型之间做出决定。
My recipe follows.
我的食谱如下。
If you want to record the physical instantat when a particular event ocurred, (a true "timestamp" , typically some creation/modification/deletion event), then use:
如果您想记录特定事件发生时的物理瞬间（真正的“时间戳”，通常是一些创建/修改/删除事件），请使用：
- Java:
Instant(Java 8 , or Jodatime). - JDBC:
java.sql.Timestamp - PostgreSQL:
TIMESTAMP WITH TIMEZONE(TIMESTAMPTZ)
- Java：
Instant（Java 8 或 Jodatime）。 - JDBC：
java.sql.Timestamp - PostgreSQL:
TIMESTAMP WITH TIMEZONE(TIMESTAMPTZ)
(Don't let PostgreSQL peculiar datatypes WITH TIMEZONE/WITHOUT TIMEZONEconfuse you: none of them actually stores a timezone)
（不要让 PostgreSQL 特有的数据类型WITH TIMEZONE/WITHOUT TIMEZONE混淆你：它们实际上都没有存储时区）
Some boilerplate code: the following assumes that psis a PreparedStatement, rsa ResultSetand tzUTCis a static Calendarobject corresponding to UTCtimezone.
一些样板代码：以下假设ps是 a PreparedStatement，rsaResultSet并且tzUTC是Calendar对应于UTC时区的静态对象。
public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
Write Instantto database TIMESTAMPTZ:
写入Instant数据库TIMESTAMPTZ：
Instant instant = ...;
Timestamp ts = instant != null ? new Timestamp(instant.toEpochMilli()) : null;
ps.setTimestamp(col, ts, tzUTC); // column is TIMESTAMPTZ!
Read Instantfrom database TIMESTAMPTZ:
Instant从数据库中读取TIMESTAMPTZ：
Timestamp ts = rs.getTimestamp(col,tzUTC); // column is TIMESTAMPTZ
Instant inst = ts !=null ? Instant.ofEpochMilli(ts.getTime()) : null;
This works safely if your PG type is TIMESTAMPTZ(In that case, the calendarUTChas 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.
如果您的 PG 类型是TIMESTAMPTZ（在这种情况下，该calendarUTC代码没有影响；但始终建议不要依赖默认时区），这可以安全地工作。“安全”意味着结果将不依赖于服务器或数据库 timezone，或时区信息：操作是完全可逆的，无论时区设置发生什么，您将始终获得与最初相同的“即时时间”爪哇方面。
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:
如果不是时间戳（物理时间线上的瞬间），而是处理“民用”本地日期时间（即字段集{year-month-day hour:min:sec(:msecs)}），则可以使用：
- Java:
LocalDateTime(Java 8 , or Jodatime). - JDBC:
java.sql.Timestamp - PostgreSQL:
TIMESTAMP WITHOUT TIMEZONE(TIMESTAMP)
- Java：
LocalDateTime（Java 8 或 Jodatime）。 - JDBC：
java.sql.Timestamp - PostgreSQL:
TIMESTAMP WITHOUT TIMEZONE(TIMESTAMP)
Read LocalDateTimefrom database TIMESTAMP:
LocalDateTime从数据库中读取TIMESTAMP：
Timestamp ts = rs.getTimestamp(col, tzUTC); //
LocalDateTime localDt = null;
if( ts != null )
localDt = LocalDateTime.ofInstant(Instant.ofEpochMilli(ts.getTime()), ZoneOffset.UTC);
Write LocalDateTimeto database TIMESTAMP:
写入LocalDateTime数据库TIMESTAMP：
Timestamp ts = null;
if( localDt != null)
ts = new Timestamp(localDt.toInstant(ZoneOffset.UTC).toEpochMilli()), tzUTC);
ps.setTimestamp(colNum,ts, tzUTC);
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.
同样，这个策略是安全的，你可以安然入睡：如果你存储了2011-10-30 23:59:30，你将始终检索那些精确的字段（小时 = 23，分钟 = 59 等），无论如何 - 即使明天你的 Postgresql 的时区服务器（或客户端）更改，或者您的 JVM 或操作系统时区，或者您所在的国家/地区是否修改了 DST 规则等。
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).
补充：如果你想（这似乎是一个自然的要求）存储完整的日期时间规范（a ZonedDatetime：时间戳和时区，它隐含地还包括完整的民用日期时间信息 - 加上时区）......那么我有坏消息给你：PostgreSQL 没有这个数据类型（据我所知，其他数据库都没有）。您必须设计自己的存储，也许在一对字段中：可以是上述两种类型（高度冗余，但对检索和计算很有效），或者其中之一加上时间偏移（您丢失时区信息，一些计算变为困难，有些是不可能的），或者其中之一加上时区（作为字符串；某些计算可能非常昂贵）。
回答by Pablo Santa Cruz
java.time
时间
It's not pretty but this is what worked for me with a ZonedDateTimeinstance using the new java.timeframework in Java 8 and later (Tutorial):
这并不漂亮，但这是在 Java 8 及更高版本（教程）中ZonedDateTime使用新java.time框架的实例对我有用的方法：
ZonedDateTime receivedTimestamp = some_ts_value;
Timestamp ts = new Timestamp(receivedTimestamp.toInstant().toEpochMilli());
ps.setTimestamp(
1,
ts,
Calendar.getInstance(TimeZone.getTimeZone(receivedTimestamp.getZone()))
);
