代码之家 › 专栏 › 技术社区 › Luiz

在PostgreSQL中使用时区正确处理时间

date-arithmetic dst timezone postgresql

Luiz · 技术社区 · 6 年前

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

以下是表格的缩写结构:

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\u DATE列表示的一天中的特定时间点。我的意思是,每个时间列都代表REPORT\u DATE中指定的一天中的一个时刻。

需要考虑的其他几点:

我们不知道为什么在从遗留系统收到的报告中,START\u HOUR是时间戳格式的。但我们按照数据的方式导入数据。
报告中的字段根据客户端的时区进行格式化,因此要重构此表,我们需要组合客户端的时区(我们有此信息),以正确插入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 将日期与时间合并,并将列更改为 带时区的时间戳 ?

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

1 回复 | 直到 6 年前

Erwin Brandstetter 3 年前

你声称:

“每个时间”列表示中指定的一天中的一个时刻 REPORT_DATE 。

所以你从不跨同一行内的a日期行。我建议保存1x 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 ( time with time zone ) should generally be avoided 。无法正确处理DST规则( D aylight公司 s aving公司 T 输入法)。

所以 基本上是你已经拥有的 .只需从中删除日期组件 start_hour ,那是空车。铸造 timestamp 到 时间 取消日期。例如: (timestamp '2018-03-25 1:00:00')::time

tz 可以是 AT TIME ZONE 构造,但要可靠地处理不同的时区,最好只使用时区名称。任何 name 您可以在 system catalog pg_timezone_names 。

为了优化存储,您可以在一个小的查找表中收集允许的时区名称并替换 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 根据需要随时显示字符串。该表用于存储您需要的信息需要。

注意括号!否则操作员 + 将在之前绑定 在时区 由于 operator precedence 。

看看结果:

数据库(&L)&燃气轮机;不停摆弄 here

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