postgresql - 在 PostgreSQL 中正确处理 TIME WITH TIME ZONE

Question

我们有一个表格，其中填充了来自另一个系统的遗留报告的数据。该表的列反射(reflect)了报告的相同结构。

这是表格的缩写结构:

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)，但其他操作如何？还有从 TIMESTAMP 中减去 TIME 的那个？

关于表重构，我们是否应该使用 TIME WITH TIME ZONE？我们应该继续使用 TIME WITHOUT TIME ZONE 吗？或者最好完全忘记 TIME 类型，将 DATE 与 TIME 结合起来，并将列更改为 TIMESTAMP WITH TIME ZONE？

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

Answer 1

您断言:

every TIME column represents a moment during the day specified in REPORT_DATE.

所以您永远不会在同一行内跨越日期变更线。我建议保存 1x date 3x time 和 time zone(作为 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 (time with time zone) should generally be avoided .它无法正确处理 DST 规则(daylight saving ttime)。

所以基本上是您已经拥有的。只需从 start_hour 中删除日期组件，这是空运费。将 timestamp 转换为 time 以截断日期。喜欢:(timestamp '2018-03-25 1:00:00')::time

tz 可以是 AT TIME ZONE 接受的任何字符串构造，但要可靠地处理不同的时区，最好只使用时区名称。您在 system catalog pg_timezone_names 中找到的任何 name .

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

带和不带 DST 的两个示例行:

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;

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

注意括号!否则运算符 + 将在 AT TIME ZONE 之前绑定(bind)，原因是 operator precedence .

看看结果:

db<> fiddle here

由于时间在维也纳被操纵(就像任何适用愚蠢 DST 规则的地方一样)，您会得到“令人惊讶”的结果。

相关:

• Accounting for DST in Postgres, when selecting scheduled items
• Ignoring time zones altogether in Rails and PostgreSQL