我应该将时区与Postgres和JDBC的时间戳分开存储吗？ [英] Should I store the timezone separately from the timestamp for Postgres and JDBC?

问题描述

似乎（也许我错了）如果你想保留JDBC和Postgres发生事件的时区，你需要将时区与时间戳分开存储。

这是我更愿意给我的ORM / JDBC / JPA一个Java 日历（或者Joda DataTime ）将时区 America / New_York 添加到Postgres timestampz 字段。并且我希望无论服务器时区（或默认为UTC）检索都能给我一个日历，时区 America / New_York 。但只看大多数JDBC代码（以及依赖它的事情都不会发生）。

这是正确的吗？

当postgres支持时，我需要将tz存储在另一个字段中，这似乎很荒谬。

因此看来似乎唯一的两个选项是：

1. 选择 timestampz Postgres列为 java.util.String 并解析它。


2. 将时区存储为单独的字段。

第一和第二选项需要某种转换拦截器用于我的SQL映射/ ORM库。

• JDBC的最佳解决方案是什么？


• JPA的最佳解决方案是什么（如果与JDBC不同）？

解决方案

存储带时区的时间戳（ timestamptz ）它被转换为UTC以存储在数据库中。检索时，它会转换为客户端的当前时区，而不是原来的时区。基本上是时间点。

还有没有时区的时间戳（ timestamp ）。这不受转换限制，但不带有时间戳。如果存储时间戳并将客户端时区设置为UTC，则在客户端时区为+08：00时检索它，您将得到相同的值。这是您想要的一半，因为它保留了原始时间值。

名称和行为很糟糕且令人困惑，但是由SQL标准设置。

如果您希望在特定时区记录某个时间点，则必须单独存储时区。我建议将其存储为 INTERVAL ，并使用 CHECK 约束将其限制为 colname间隔'-12'小时+间隔'1'第二和间隔'12'小时。该定义拒绝-12：00并接受+12：00;我不完全确定这是正确的，所以请检查。

您可以存储本地时间的时间戳该时区（我可能会做什么），或者存储事件发生时UTC时间的 timestamptz 加上一个可以将其转换为本地时间的偏移量。 / p>

两者都适用于JDBC。对于JPA，它取决于您的提供者理解的程度并映射区间类型。理想情况下，您希望实体中的瞬态生成字段使用存储在时间戳和间隔中重构所需的Calendar实例。数据库。

It seems (and maybe I'm wrong) that if you want to preserve the timezone of when something happened with JDBC and Postgres you need to store the timezone separately from the timestamp.

That is I would prefer to give my ORM/JDBC/JPA a Java Calendar (or Joda DataTime) with say timezone America/New_York to a Postgres timestampz field. AND I would expect on retrieval regardless of the Servers timezone (or defaulting to UTC) to give me back a Calendar with timezone America/New_York. But just looking at most JDBC code (and things that depend on it that doesn't happen).

Is this correct?

This seems ridiculous that I would need to store the tz in another field when postgres supports it.

Thus it seems like the only two options are:

1. Select the timestampz Postgres column as a java.util.String and parse it.
2. Store the timezone as a separate field.

Option number one and two one would require some sort of conversion interceptors for my SQL mapping / ORM libraries.

• What is the best solution for JDBC ?
• What is the best solution for JPA (if different than JDBC)?

解决方案

When you store a timestamp with time zone (timestamptz) it's converted to UTC for storage in the DB. When retrieved, it's converted to the client's current timezone, not the timezone it was originally in. It's a point in time, basically.

There is also timestamp without time zone (timestamp). This is not subject to conversion, but does not carry a timestamp with it. If you store a timestamp with your client time zone set to UTC, then retrieve it when the client time zone is '+08:00', you get the same value. That's half what you want, in that it preserves the raw time value.

The names and behaviours are awful and confusing, but set by the SQL standard.

You must store the time zone separately if you wish to record a point in time at a particular time zone. I'd recommend storing it as an INTERVAL with a CHECK constraint limiting it to be colname BETWEEN INTERVAL '-12' HOUR + INTERVAL '1' SECOND AND INTERVAL '12' HOUR. That definition rejects -12:00 and accepts +12:00; I'm not totally sure that's right, so check.

You could either store the timestamp of local time at that time zone (what I'd probably do), or store the timestamptz of the UTC time when the event occurred plus an offset that lets you convert it to local time.

Either will work fine for JDBC. For JPA, it'll depend on how well your provider understands and maps interval types. Ideally you want a transient generated field in your entity that reconstructs the Calendar instance you want using the timestamp and interval stored in the database.

这篇关于我应该将时区与Postgres和JDBC的时间戳分开存储吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！