按时间戳日期分组的时区适配问题及SQL统计修正方案咨询

问题根源在于你当前用DATE(created_ts)是基于UTC时区取日期，但用户实际要的是**上海时区（UTC+8）**的日期分组。上海时区的2024-12-17 00:00:00对应UTC的2024-12-16 16:00:00，这段时间内的记录在UTC日期里属于16日，但在上海时区属于17日，所以原查询会把这部分错误分到16日分组，导致统计偏差。

以下是针对不同数据库的具体解决方法：

MySQL 解决方案 #

使用CONVERT_TZ函数将UTC时间转换为上海时区，再基于转换后的时间做筛选和分组：

SELECT COUNT(*) AS user_count,
       DATE(CONVERT_TZ(created_ts, '+00:00', '+08:00')) AS shanghai_date
FROM users
WHERE CONVERT_TZ(created_ts, '+00:00', '+08:00') BETWEEN '2024-12-17 00:00:00' AND '2024-12-17 15:59:59'
GROUP BY shanghai_date;

注意：如果MySQL报错提示找不到时区信息，Linux环境可执行mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql导入时区表。

PostgreSQL 解决方案 #

使用AT TIME ZONE语法转换时区：

SELECT COUNT(*) AS user_count,
       DATE(created_ts AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai') AS shanghai_date
FROM users
WHERE created_ts AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' BETWEEN '2024-12-17 00:00:00' AND '2024-12-17 15:59:59'
GROUP BY shanghai_date;

核心思路 #

所有时间处理（筛选、分组）都统一基于用户选择的时区（上海时区），而不是存储的UTC时区。这样就能保证统计结果和用户选择的时间范围完全匹配，不会出现跨时区的分组错误。

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