Postgres/TimescaleDB夏令时切换日如何查询对应数量的30分钟间隔测量数据

问题根因 #

你当前使用timestamp without timezone类型存储采集时间，直接按固定24小时的日期起止范围做匹配时，没有适配法国夏令时切换导致的本地自然日对应UTC时间长度变化，因此每次查询都只会返回固定48条数据。

解决方案 #

1. 核心逻辑调整 #

你需要先将法国时区下的目标查询日期，转换为对应实际的UTC时间范围，再用该范围匹配存储的时间字段：

• 2021-03-28（夏令时启动，当日少1小时）：对应UTC范围总长度23小时，刚好返回46条30分钟间隔数据
• 2020-10-25（夏令时结束，当日多1小时）：对应UTC范围总长度25小时，刚好返回50条30分钟间隔数据

可以直接用Postgres自带的时区转换函数自动计算范围，不需要硬编码特殊日期：

-- 自动生成法国时区下目标日期对应的UTC起止时间
SELECT
  (目标日期::date || ' 00:00:00')::timestamp AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' AS utc_start,
  ((目标日期::date + INTERVAL '1 day') || ' 00:00:00')::timestamp AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' AS utc_end

如果你的time字段存储的是法国本地时间而非UTC，将上面语句中的UTC和Europe/Paris顺序调换即可。

2. Eloquent查询改写 #

把你原有的查询逻辑调整为使用转换后的UTC范围匹配：

// 计算目标日期对应的UTC起止时间
$targetDate = '2021-03-28'; // 替换为实际查询日期
$utcStart = DB::selectOne("SELECT (?::date || ' 00:00:00')::timestamp AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' AS start", [$targetDate])->start;
$utcEnd = DB::selectOne("SELECT ((?::date + INTERVAL '1 day') || ' 00:00:00')::timestamp AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' AS end", [$targetDate])->end;

$measuresByTS = Measure::where('time', '>=', $utcStart)
    ->where('time', '<', $utcEnd) // 替换原有的<=，避免包含下一天首条数据
    ->...

3. TimescaleDB内置函数支持 #

如果你需要按法国本地时间做时间粒度聚合，可以直接用time_bucket的时区适配能力，自动处理夏令时切换的偏移：

-- 按法国本地时间30分钟粒度聚合，自动适配夏令时
SELECT time_bucket('30 minutes', time AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Paris') AS local_bucket,
  avg(measure_value) AS avg_value
FROM measures
GROUP BY local_bucket
ORDER BY local_bucket;

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