在PostgreSQL中正确处理带时区的时间

Question

在PostgreSQL中正确处理带时区的时间

5

我们有一张表，其中填充着来自另一个系统的旧报告数据。该表的列反映了报告的相同结构。

以下是表的简化结构：

CREATE TABLE IF NOT EXISTS LEGACY_TABLE (
  REPORT_DATE DATE NOT NULL,
  EVENT_ID BIGINT PRIMARY KEY NOT NULL,
  START_HOUR TIMESTAMP WITHOUT TIME ZONE,
  END_HOUR TIME WITHOUT TIME ZONE,
  EXPECTED_HOUR TIME WITHOUT TIME ZONE
);

我们正在重构这个表格以处理不同客户的不同时区。新结构可能如下所示:

CREATE TABLE IF NOT EXISTS LEGACY_TABLE (
  REPORT_DATE DATE NOT NULL,
  EVENT_ID BIGINT PRIMARY KEY NOT NULL,
  START_HOUR TIMESTAMP WITH TIME ZONE,
  END_HOUR TIME WITH TIME ZONE,
  EXPECTED_HOUR TIME WITH TIME ZONE
);

这些时间字段代表了 REPORT_DATE 列所代表的一天中的特定时间点。我的意思是，每个 TIME 列都代表在 REPORT_DATE 指定的一天中的某个时刻。

还有其他几点需要考虑：

- 我们不知道为什么我们从旧系统接收到的 START_HOUR 是 TIMESTAMP 格式。但是我们会按照原样导入数据。 - 报告中的字段根据客户端的时区进行格式化，因此为了重构此表，我们需要将客户端的时区（我们有这些信息）与 UTC 正确地插入时间戳/时间。但现在问题来了。这些列的值被用于多次计算我们系统中的其他值，类似于以下内容：

START_HOUR - END_HOUR (the result of this operation is currently being casted to TIME WITHOUT TIME ZONE)
START_HOUR < END_HOUR
START_HOUR + EXPECTED_HOUR
EXPECTED_HOUR - END_HOUR
EXPECTED_HOUR < '05:00'

经过一些研究，我发现不推荐使用类型TIME WITH TIME ZONE(Postgres time with time zone equality)，现在我有点困惑，关于重构处理不同时区和处理我们需要的不同列操作，最好的方法是什么。

此外，我已经知道安全地减去两个TIMESTAMP WITH TIME ZONE类型的列。这个减法操作考虑了DST的变化(Subtracting two columns of type timestamp with time zone)，但其他情况呢？如果从一个时间戳中减去一个时间呢？

至于表格重构，我们应该仍然使用TIME WITHOUT TIME ZONE吗？还是更好地忘记TIME类型，将日期与时间组合，并将列更改为TIMESTAMP WITH TIME ZONE？

我认为这些问题是相关的，因为我们选择使用的新列类型将定义我们如何操作列。

- Luiz

我个人偏爱使用一个整数列来保存Unix时间戳，尽管在2038年你需要更改该列的类型。 - BShaps

请问，您目前忽略了START_HOUR的日期部分吗？或者您是否有一个CHECK约束来确保它与REPORT_DATE相同？ - Erwin Brandstetter

@ErwinBrandstetter：目前我们只保存日期部分，但是在所有当前的计算中，我们只使用时间部分。当我们处理旧报告时，我们确保日期部分和 REPORT_DATE 是相同的。 - Luiz

我们需要将客户端的时区（我们已经有这个信息）进行合并。你是如何在不保存信息的情况下获得这些信息的？ - Erwin Brandstetter

我们将其保存在客户表中。我们知道哪个客户正在导入报告，因此也知道时区。 - Luiz

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

您断言：

每个时间列都代表在REPORT_DATE指定的一天中的某个时刻。

因此，在同一行内您从未跨越日期变更线。我建议保存1个date，3个time和时区（作为text或FK列）：

CREATE TABLE legacy_table (
   event_id      bigint PRIMARY KEY NOT NULL
 , report_date   date NOT NULL
 , start_hour    time
 , end_hour      time
 , expected_hour time
 , tz            text  -- time zone
);

就像你已经发现的那样，应该尽量避免使用timetz（带时区的时间）。它无法正确处理DST规则（夏令时）。

所以基本上你已经有了。只需从start_hour中删除日期组件，这是无用的。将timestamp转换为time以截断日期。例如：(timestamp '2018-03-25 1:00:00')::time

tz可以是任何被AT TIME ZONE结构所接受的字符串，但为了可靠地处理不同的时区，最好仅使用时区名称。在系统目录pg_timezone_names中可以找到任何name。

为了优化存储，您可以在一个小的查找表中收集允许的时区名称，并将tz text替换为tz_id int REFERENCES my_tz_table。

两个带有和不带有夏令时的示例行：

INSERT INTO legacy_table VALUES
   (1, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Vienna')  -- sadly, with DST
 , (2, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Moscow'); -- Russians got rid of DST

为了表示或计算，您可以执行以下操作：

SELECT (report_date + start_hour)    AT TIME ZONE tz AT TIME ZONE 'UTC' AS start_utc
     , (report_date + end_hour)      AT TIME ZONE tz AT TIME ZONE 'UTC' AS end_utc
     , (report_date + expected_hour) AT TIME ZONE tz AT TIME ZONE 'UTC' AS expected_utc
     -- START_HOUR - END_HOUR
     , (report_date + start_hour) AT TIME ZONE tz
     - (report_date + end_hour)   AT TIME ZONE tz AS start_minus_end
FROM   legacy_table;

您可以创建一个或多个视图，以便根据需要轻松显示字符串。该表用于存储您需要的信息。

请注意括号！否则，由于运算符优先级，运算符+将在AT TIME ZONE之前绑定。

看一下结果吧： db<>fiddle 在这里 由于时间在维也纳进行操作（就像任何适用愚蠢DST规则的地方一样），因此您会得到“惊人”的结果。