时间戳问题

带时区时间戳的提升转换

在 SQL 中处理时区有时会非常容易混淆。例如，按日期范围过滤时，你可能会写出如下查询：

SET timezone = 'America/Los_Angeles';

CREATE TABLE times AS
    FROM range('2025-08-30'::TIMESTAMPTZ, '2025-08-31'::TIMESTAMPTZ, INTERVAL 1 HOUR) tbl(t);

FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│ 2025-08-30 00:00:00-07   │
└──────────────────────────┘

但如果切换到其他时区，查询结果也会变化：

SET timezone = 'HST';
FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│ 2025-08-29 21:00:00-10   │
│ 2025-08-29 22:00:00-10   │
│ 2025-08-29 23:00:00-10   │
│ 2025-08-30 00:00:00-10   │
└──────────────────────────┘

甚至更糟：

SET timezone = 'America/New_York';
FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│          0 rows          │
└──────────────────────────┘

这些令人困惑的结果源于 SQL 从 DATE 到 TIMESTAMP WITH TIME ZONE 的转换规则。该转换会把日期提升为当前时区的午夜时刻。

一般来说，除非你确实需要用当前时区做展示（或进行其他时间分桶），否则建议时序数据使用普通 TIMESTAMP。这样可避免此类困惑，且算术运算通常更快。

时区性能

Goose 使用 International Components for Unicode（ICU）时间库提供时区支持。该库有多个优势，包括支持 2037 年之后的夏令时。（注意：Pandas 在该年份之后会给出错误结果。）

使用 ICU 的代价是性能不算高。一种常见优化方式是为目标时间范围预先构建日历表。例如，如果业务要按小时粒度建模到 2100 年的供需数据，可这样创建日历表：

SET timezone = 'Europe/Amsterdam';

CREATE OR REPLACE TABLE hourly AS
    SELECT 
        ts, 
        year::SMALLINT AS year,
        month::TINYINT AS month,
        day::TINYINT AS day,
        hour::TINYINT AS hour,
    FROM (
        SELECT ts, unnest(date_part(['year', 'month', 'day', 'hour',], ts))
        FROM generate_series(
            '2020-01-01'::DATE::TIMESTAMPTZ, 
            '2100-01-01'::DATE::TIMESTAMPTZ, 
            INTERVAL 1 HOUR) tbl(ts)
    ) parts;

然后你可以把这张约 70 万行的表与任意时间戳列 join，快速获得目标时区下的时间分桶值。内部 cast 不是必须，但能让表更小，因为 date_part 默认对所有部分返回 64 位整数。

注意，我们可以一次调用 date_part 就提取全部部分。这种“部件列表”写法比逐个提取更快，因为底层分桶计算本来就会算出所有部分，按列表取值可避免重复调用较慢的 ICU 函数。

另外，这里也利用了上一节 DATE 的 cast 规则，把日历范围限制在模型域内。

半开区间

在 SQL 里做时序分析时，另一个容易忽视的问题是 BETWEEN。时序分析几乎总是使用半开分桶区间，以避免边界重叠。但 BETWEEN 实际上是闭区间：

x BETWEEN begin AND end
-- expands to
begin <= x AND x <= end
-- not
begin <= x AND x < end

为避免这个问题，请显式写比较边界，而不要直接用 BETWEEN。

带时区时间戳的提升转换​

时区性能​

半开区间​

带时区时间戳的提升转换

时区性能

半开区间