如何将OffsetDateTime存储到PostgreSQL“带时区的时间戳”。柱 [英] How to store OffsetDateTime to PostgreSQL "timestamp with time zone" column

问题描述

我正在尝试使用JDBC将 OffsetDateTime（ 2019-01-14 21：10：00.02 + 03）与时区（+03）存储到PostgreSQL。
但是当使用sql查询检索数据时，我总是得到+00结果。
是否可以在postgres中存储带日期时间的偏移量（+03）？

I am trying to store OffsetDateTime("2019-01-14 21:10:00.02+03") with timezone(+03) using JDBC to PostgreSQL. But when retrieve data using sql query I always get the +00 result. Is there any way to store offset (+03) with datetime in postgres ?

推荐答案

是否可以在postgres中使用日期时间存储偏移量（+03）？

Is there any way to store offset (+03) with datetime in postgres ?

是，但是正如@Andreas所说，偏移量必须在单独的列中。 PostgreSQL中带有时区的列类型名称 timestamp 有点误导；它实际上只是时间戳转换为utc 。原始偏移量不是 与时间戳记值一起存储的。

Yes, but as @Andreas says, the offset will have to be in a separate column. The column type name timestamp with time zone in PostgreSQL is a bit misleading; it is really just timestamp converted to utc. The original offset is not stored with the timestamp value.

现在，您可能会想添加一个新的 varchar 列，并将偏移量另存为 hh：mm 字符串，例如

Now you might be tempted to add a new varchar column and save the offset as a hh:mm string, e.g.,

id  twtz                    twtz_offset
--  ----------------------  -----------
 1  2019-01-15 07:00:00+00  -07:00

，但实际上最好使用 ZoneOffset＃getTotalSeconds（）在几秒钟内提取偏移量 并将其存储在整数列，例如

but in fact it would be better to extract the offset in seconds using ZoneOffset#getTotalSeconds() and store that in an integer column, e.g.,

id  twtz                    twtz_offset
--  ----------------------  -----------
 1  2019-01-15 07:00:00+00       -25200

因为该数字可以直接应用于UTC值转换回原始的 OffsetDateTime 而不必转换偏移量从 String 到 int

because that number can be applied directly to a UTC value to convert back to the original OffsetDateTime without having to translate the offset from String to int

OffsetDateTime odtRetrieved = rs.getObject("twtz", OffsetDateTime.class);  // always UTC for PostgreSQL
// odtRetrieved is 2019-01-15T07:00Z
OffsetDateTime odtOriginal = odtRetrieved
        .toInstant()
        .atOffset(ZoneOffset.ofTotalSeconds(rs.getInt("twtz_offset")));
// odtOriginal is 2019-01-15T00:00-07:00

如果您发现自己实际上处理的不是整分钟，则还提供了一些额外的鲁棒性。

and it also offers some extra robustness in case you ever find yourself dealing with an oddball offset that is in fact not a whole minute.

（如果您认为从不然后发生，请留意乔恩·斯基特（Jon Skeet）即将出版的科技/恐怖小说《时区规则几乎打动了我的大脑》。基于真实的故事。很快就会在书店中出现。）

(If you think that will never happen then keep an eye out for Jon Skeet's upcoming tech/horror novel "Time Zone Rules Almost Broke My Brain". Based on a true story. In bookstores soon.)

这篇关于如何将OffsetDateTime存储到PostgreSQL“带时区的时间戳”。柱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！