如何将timestamptz截断至本地时区的当月起始时间？

当在DuckDB中设置时区为Europe/Amsterdam后，使用time_bucket('1 month', a)处理带时区的时间戳时，会出现返回结果偏移2小时的问题——实际得到2020-10-01 02:00:00+02，但期望是本地时区当月1日的00:00:00+02。

这是因为time_bucket默认基于UTC计算时间桶的起始点，转换到欧洲阿姆斯特丹时区后就会产生偏移。要实现按本地时区对齐的当月起始时间，可以通过以下两种方式处理：

方法一：类型转换截断 #

先将timestamptz转换为本地时区的timestamp（自动应用当前设置的时区），用DATE_TRUNC截断到月初，再转回timestamptz：

-- 设置时区
SET timezone = 'Europe/Amsterdam';

-- 查询语句
SELECT 
    a,
    CAST(DATE_TRUNC('month', CAST(a AS TIMESTAMP)) AS TIMESTAMPTZ) AS time_bucket_local
FROM rel;

执行后得到期望结果：

┌──────────────────────────┬───────────────────────────┐
│            a             │ time_bucket_local         │
│ timestamp with time zone │ timestamp with time zone  │
├──────────────────────────┼───────────────────────────┤
│ 2020-10-25 00:00:00+02   │ 2020-10-01 00:00:00+02    │
└──────────────────────────┴───────────────────────────┘

方法二：显式时区转换 #

通过TIMEZONE函数显式指定时区，将timestamptz转换为本地时间后截断，再转回带时区的时间：

SET timezone = 'Europe/Amsterdam';

SELECT 
    a,
    TIMEZONE('Europe/Amsterdam', DATE_TRUNC('month', TIMEZONE('Europe/Amsterdam', a))) AS time_bucket_local
FROM rel;

核心逻辑 #

两种方法本质都是先把带时区的时间「落地」到本地时区的无时区时间，完成月初截断操作后，再恢复为时区类型，确保起始时间是本地时区的00:00点，而非UTC时区转换后的偏移时间。

内容的提问来源于stack exchange，提问作者ignoring_gravity