TimescaleDB chunk和time_bucket原点

chunk内部时间值始终基于 Unix Epoch第 1 步PostgreSQL → 内部值转换utils.c:64-82 —ts_pg_timestamp_to_unix_microseconds// 第 82 行是核心 PG_RETURN_INT64(timestamp TS_EPOCH_DIFF_MICROSECONDS);这里把 PG 内部时间戳距 2000-01-01 的微秒数加上偏移量转为距 Unix epoch 的微秒数。第 2 步偏移量的定义time_utils.h:13-14#define TS_EPOCH_DIFF (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) #define TS_EPOCH_DIFF_MICROSECONDS (TS_EPOCH_DIFF * USECS_PER_DAY)POSTGRES_EPOCH_JDATE 2000-01-01 的 Julian DateUNIX_EPOCH_JDATE 1970-01-01 的 Julian Date差值 10957 天(30年中的闰年已计入)第 3 步内部值用于 Chunk 计算utils.c:183-186 —ts_time_value_to_internal调用上述转换case TIMESTAMPTZOID: res DirectFunctionCall1(ts_pg_timestamp_to_unix_microseconds, time_val); return DatumGetInt64(res);dimension.c:291 — 用 Unix epoch 微秒值计算 chunk 边界range_start (value / dim-fd.interval_length) * dim-fd.interval_length;完整转换链2026-02-12 00:00:00 UTC ↓ PG 内部: 9539天微秒 (距 2000-01-01) ↓ ts_pg_timestamp_to_unix_microseconds (10957天微秒) Unix 微秒: 20496天微秒 (距 1970-01-01) ← 这才是 dimension.c:291 的 value ↓ range_start (20496 / 7) * 7 20496 天 ↓ chunk 起始 1970-01-01 20496天 2026-02-12 00:00:00 UTC ✅结论TimescaleDB的chunk是以 Unix epoch1970-01-01为时间原点。time_bucket(1 week)的原点是2000-01-03周一不是 Unix epoch关键代码在 time_bucket.c:94-101#define JAN_3_2000 (2 * USECS_PER_DAY) /* * The default origin is Monday 2000-01-03. We dont use PG epoch since it starts on a saturday. * This makes time-buckets by a week more intuitive and aligns it with date_trunc. Since month * bucketing ignores the day component this makes origin for month buckets 2000-01-01. */ #define DEFAULT_ORIGIN (JAN_3_2000)注释写得非常清楚PostgreSQL epoch2000-01-01是周六如果以它作为周的起点会很别扭所以选了 2000-01-03周一作为默认原点这样time_bucket的周就自然对齐到周一。Chunk 切割 vs time_bucket — 原点完全不同Chunk 切割time_bucket(1 week)原点Unix epoch(1970-01-01周四)Monday 2000-01-03周一代码位置dimension.c:291time_bucket.c:1012026-02-12 结果2026-02-12周四2026-02-09周一TIME_BUCKET_TS 宏的计算逻辑 (time_bucket.c:102-135)time_bucket(1 week, 2026-02-12): 1. timestamp 9539天 (距 2000-01-01) 2. shift origin 2天 (即 2000-01-03) 3. timestamp - shift → 9537天 4. result (9537 / 7) * 7 → 9534天 5. result shift → 9536天 → 2026-02-09 (周一) ✅而对 chunk 来说(value / interval) * interval (20496 / 7) * 7 20496天 → 2026-02-12 (周四)总结Chunk 和 time_bucket 的周边界可能对不齐——chunk 对齐到 Unix epoch周四基准time_bucket对齐到周一。如果需要time_bucket也和 chunk 对齐可以传入第三个参数自定义 origin-- 让 time_bucket 以 1970-01-01 为原点和 chunk 对齐 SELECT time_bucket(1 week, timestamp, 1970-01-01::timestamptz);