在 PostgreSQL 中正确处理 TIME AND TIME ZONE

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

以下是该表的缩写结构：

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 时间与时区相等 https://stackoverflow.com/questions/20529284/postgres-time-with-time-zone-equality/20530283）现在我有点困惑重构这个表以处理不同时区并处理我们需要的不同列操作的最佳方法是什么。

除此之外，我已经知道减去两列类型是安全的TIMESTAMP WITH TIME ZONE。此减法运算考虑了 DST 变化（用时区减去时间戳类型的两列 https://stackoverflow.com/questions/50223332/subtracting-two-columns-of-type-timestamp-with-time-zone）但是其他人呢？还有从时间戳中减去时间的那个？

关于表重构，我们应该使用TIME WITH TIME ZONE无论如何？我们应该继续使用TIME WITHOUT TIME ZONE？或者最好忘记类型TIME总共并将日期与时间合并并将列更改为TIMESTAMP WITH TIME ZONE?

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

您断言：

每个 TIME 列代表一天中指定的时刻REPORT_DATE.

So you never跨越同一行内的日期变更线。我建议保存1xdate 3x time和时区 (as 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) 一般应避免 https://stackoverflow.com/a/20530283/939860。它无法正确处理 DST 规则（daylight saving time).

So 基本上是你已经拥有的。只需删除日期组件即可start_hour，那是死运费。投掷timestamp to time来截断日期。喜欢：(timestamp '2018-03-25 1:00:00')::time

tz可以是任何接受的字符串AT TIME ZONE https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT构造，但为了可靠地处理不同的时区，最好专门使用时区名称。任何name你发现在系统目录pg_timezone_names https://www.postgresql.org/docs/current/view-pg-timezone-names.html.

为了优化存储，您可以在小型查找表中收集允许的时区名称并替换tz text with 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 https://www.postgresql.org/docs/current/sql-createview.html以便根据需要轻松显示字符串。该表用于存储您的信息need.

注意括号！否则运营商+之前会绑定AT TIME ZONE due to 运算符优先级 https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE.

看看结果：

数据库小提琴

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

Related:

• 选择计划项目时考虑 Postgres 中的 DST https://stackoverflow.com/questions/13239534/accounting-for-dst-in-postgres-when-selecting-scheduled-items/13243029#13243029
• 在 Rails 和 PostgreSQL 中完全忽略时区 https://stackoverflow.com/questions/9571392/ignoring-time-zones-altogether-in-rails-and-postgresql/9576170#9576170