timescaleDB超表时间分桶时区不正确

Question

问题：使用超表，时间间隔为1天，就会产生按照时间序列分子表跨8个时区现象，（首先确认的是时区没问题，select now();，和系统一致，show timezone，显示 'Asia/Shanghai'），现象是比如2024年1月15日的数据 '2024-01-15 01:00:00'，会分配到1月14日数据子表，但是 '2024-01-15 09:00:00'，就会分配到1月15日数据子表中，最后的结果是 time_bucket()函数结果不对。

操作步骤

-- 建表语句
create table test(
  time timestamptz not null,
 type int4 not null,
price float8 not null
);
-- 建立超表
select create_hypertable('test', 'time', chunk_time_interval => interval '1 day');
 
-- 插入数据
insert into test values('2024-01-25 00:03:28',1,1);
insert into test values('2024-01-25 10:03:28',1,1);
insert into test values('2024-01-26 00:03:28',1,1);
insert into test values('2024-01-26 06:01:28',1,1);
insert into test values('2024-01-26 10:01:28',1,1);
insert into test values('2024-01-27 00:03:28',1,1);
insert into test values('2024-01-28 00:03:28',1,1);
insert into test values('2024-01-29 00:03:28',1,1);

select * from test 的结果为：

select show_chunks('test') 为：

查找子表 select * from _timescaledb_internal._hyper_9_82_chunk

按道理来讲，不同日期应该在不同分区，但是结果却是往前搓了8个小时，当然执行 time_bucket() 函数也是错的，哪位小伙伴可以帮忙看看这个问题呢

Answer 1

根据你所提供的信息，出现你所描述问题的原因可能是 TimescaleDB 在创建 hypertable 时默认以 UTC 时间为基准进行时间划分。当你设置 chunktimeinterval 为 '1 day' 时，实际上是以 UTC 时间每天为一个块进行划分，而不是你设定的 'Asia/Shanghai' 时区。这就导致 '2024-01-15 01:00:00'（Shanghai 时间）被划分到 '2024-01-14' 的块中（因为此时 UTC 时间为 '2024-01-14 17:00:00'），'2024-01-15 09:00:00'（Shanghai 时间）被划分到 '2024-01-15' 的块中（此时 UTC 时间为 '2024-01-15 01:00:00'）。
TimescaleDB 的这一设计可能并不符合你的期望，但这是 TimescaleDB 目前的默认行为。目前有关于这一问题的讨论在 TimescaleDB 的 GitHub issue 上，你可以查看：
https://github.com/timescale/timescaledb/issues/341
如果要按照你的期望来进行时间划分，你可能需要在查询时将时间转为 UTC，如：

SELECT time_bucket('1 day', time AT TIME ZONE 'UTC') AS day, AVG(price)
FROM test
GROUP BY day
ORDER BY day;

这样就可以得到按 'Asia/Shanghai' 时区每天进行划分的结果。
以上就是我对你问题的理解和建议，如果还需要进一步讨论，欢迎留言。