node.js - PostgreSQL 中跨多个时区的总时差

Question

假设我有一个包含两个 TIMESTAMPTZ 列的表 - start_time 和 end_time。

在我的 JS 代码中，我将使用 momentjs 创建开始点和结束点，我想找到所有带有 end_time 在这两点之间，不管每条记录的时区如何。

通常，如果表中的所有记录都使用相同的时区(例如 UTC)，我会做类似的事情:

SELECT sum(EXTRACT(epoch from (end_time - start_time) / 3600)) as hours_used FROM table

但我不确定如何在考虑不同时区的情况下完成此操作。主要担心的是我不想意外遗漏几行，因为它们与我开始时处于不同的时区/终点。

Answer 1

您的查询是有效的原样，一旦您附加了一个WHERE 子句来实现您想要的过滤器:

for all records with an end_time between those two points

SELECT sum(EXTRACT(epoch from (end_time - start_time) / 3600)) as hours_used
FROM   tbl
WHERE  end_time BETWEEN $momentjs_start AND $momentjs_end;

其中 $momentjs_start 和 $momentjs_end 是您创建的起点和终点，它们也应该是 timestamptz。 (对于 timestamp 值， session 的当前时区假定在赋值转换中)。 BETWEEN 包括下限和上限，顺便说一句。

您的困惑可能是由于 不幸的 name 数据类型 timestamp with time zone (= timestamptz), 由 SQL 标准委员会定义。它实际上并不存储任何时区信息。 timestamptz 文字中的时区偏移量、缩写或名称仅用作输入/输出修饰符以针对时区进行调整。在内部，存储纯 UTC 时间戳。所以您的计算按原样进行。

所以，这是给定的，自动的:

regardless of the timezone on each individual record.

相关:

• Ignoring timezones altogether in Rails and PostgreSQL
• Time zone storage in data type "timestamp with time zone"