时间戳问题 – DuckDB - DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / 指南 / SQL 功能

时间戳问题

带时区时间戳的提升转换

在 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          │
└──────────────────────────┘

这些令人困惑的结果归因于从 DATE 到 TIMESTAMP WITH TIME ZONE 的 SQL 转换规则。此转换是为了将日期提升为当前时区的午夜时间。

通常情况下，除非你需要使用当前时区进行显示（或进行其他时间分箱操作），否则你应该对时间数据使用普通的 TIMESTAMP。这将避免此类令人困惑的问题，且算术运算通常也更快。

时区性能

DuckDB 使用 International Components for Unicode (ICU) 时间库来支持时区。该库具有许多优势，包括支持 2037 年之后的夏令时。（注：Pandas 在计算超过该年份的数据时会给出错误结果）。

使用 ICU 的缺点是其性能并不高。一种解决方法是为所建模的时间戳创建一个日历表。例如，如果应用程序需要对直到 2100 年的电力供需进行小时级建模，可以按如下方式创建日历表

SET timezone = 'Europe/Netherlands';

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 万行的表与任何时间戳列进行连接，以快速获得目标时区的时间分箱值。内部转换虽然不是必须的，但可以生成更小的表，因为 date_part 会为所有部分返回 64 位整数。

注意，我们可以通过一次调用 date_part 来提取所有部分。这种函数的部分列表版本比逐个提取部分要快，因为底层的分箱计算会计算出所有部分，因此从列表中挑选所需部分避免了对缓慢的 ICU 函数的重复调用。

还要注意，我们利用了上一节中的 DATE 转换规则来限定日历表的模型范围。

半开区间

在 SQL 中进行时间分析时，另一个微妙的问题是 BETWEEN 运算符。时间分析几乎总是使用半开分箱区间，以避免两端出现重叠。不幸的是，BETWEEN 运算符是一个闭合区间

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

为了避免这个问题，请确保明确比较边界，而不是使用 BETWEEN。

关于此页面

行为准则商标使用指南

带时区时间戳的提升转换

时区性能

半开区间

关于此页面

本文中