如何将多时区小时计算转换为PST后进行每日汇总计算？

我明白你现在的困扰——已经有了按小时计算各实体数值的存储过程，但要把这些数据统一转成PST时区再做每日汇总，还要兼顾夏令时和不同时区实体的差异，分组逻辑确实容易绕晕。我来帮你拆解清楚，一步步搞定：

核心思路：先统一时间到PST，再按PST日期分组 #

不管实体原来在哪个时区，我们的目标是把每一条小时级数据，映射到它对应的PST日期，然后就可以简单地按「实体ID + PST日期」来汇总总量。关键在于准确完成时区转换，尤其是夏令时的处理。

分场景处理的具体逻辑 #

场景1：实体不实行夏令时（utc_offset固定） #

这种情况最简单，因为实体的时区偏移不会随时间变化。假设你的小时级数据里：

• local_hour：该实体本地时间的小时起始点（比如2024-05-20 10:00:00）
• utc_offset：实体相对于UTC的固定偏移（比如东八区是+8）
• value：该小时的数值

我们需要先把实体本地时间转成UTC，再转成PST，最后提取PST日期：

-- 先定义PST的UTC偏移（注意：标准PST是UTC-8，PDT夏令时是UTC-7，你说的UTC_OFFSET=5可能是笔误，这里按标准示例）
SET @pst_offset = '-08:00';

SELECT
    entity_id,
    -- 转换步骤：本地时间 → UTC → PST，然后提取日期
    DATE(CONVERT_TZ(local_hour, CONCAT(SUBSTRING_INDEX(utc_offset, '', 1), LPAD(ABS(utc_offset), 2, '0'), ':00'), @pst_offset)) AS pst_date,
    SUM(value) AS daily_total
FROM your_hourly_table
GROUP BY entity_id, pst_date;

如果你的原始小时时间是UTC时间（不是本地时间），那更简单，直接转PST即可：

SELECT
    entity_id,
    DATE(CONVERT_TZ(utc_hour, '+00:00', @pst_offset)) AS pst_date,
    SUM(value) AS daily_total
FROM your_hourly_table
GROUP BY entity_id, pst_date;

场景2：实体实行夏令时（utc_offset随日期变化） #

这种情况需要先判断该小时是否处于夏令时区间，调整实体的实际偏移量，再做转换。以美国东部时区为例（EST是UTC-5，EDT夏令时是UTC-4），可以用日期逻辑判断夏令时：

SET @pst_offset = '-08:00';

SELECT
    entity_id,
    DATE(CONVERT_TZ(
        local_hour,
        -- 动态计算实体当前的实际UTC偏移
        CASE
            WHEN local_hour BETWEEN 
                -- 3月第二个周日2点开始夏令时
                DATE_FORMAT(local_hour, '%Y-03-01') + INTERVAL (14 - DAYOFWEEK(DATE_FORMAT(local_hour, '%Y-03-01'))) DAY + INTERVAL 2 HOUR
                AND 
                -- 11月第一个周日2点结束夏令时
                DATE_FORMAT(local_hour, '%Y-11-01') + INTERVAL (7 - DAYOFWEEK(DATE_FORMAT(local_hour, '%Y-11-01'))) DAY + INTERVAL 2 HOUR
            THEN CONCAT('-', LPAD(ABS(utc_offset) - 1, 2, '0'), ':00') -- 夏令时偏移减1
            ELSE CONCAT(SUBSTRING_INDEX(utc_offset, '', 1), LPAD(ABS(utc_offset), 2, '0'), ':00')
        END,
        @pst_offset
    )) AS pst_date,
    SUM(value) AS daily_total
FROM your_hourly_table
WHERE entity_id IN (你的夏令时实体ID列表)
GROUP BY entity_id, pst_date

-- 再union上不实行夏令时的实体数据
UNION ALL

SELECT
    entity_id,
    DATE(CONVERT_TZ(local_hour, CONCAT(SUBSTRING_INDEX(utc_offset, '', 1), LPAD(ABS(utc_offset), 2, '0'), ':00'), @pst_offset)) AS pst_date,
    SUM(value) AS daily_total
FROM your_hourly_table
WHERE entity_id NOT IN (你的夏令时实体ID列表)
GROUP BY entity_id, pst_date;

关键注意事项 #

1. 确认原始时间的时区：一定要搞清楚你的local_hour或utc_hour字段是UTC时间还是实体本地时间，这直接决定转换公式的方向。
2. PST偏移的准确性：标准PST是UTC-8，PDT夏令时是UTC-7，如果你说的UTC_OFFSET=5是特定业务定义，记得替换成对应的偏移值。
3. 数据库时区数据更新：如果用数据库自带的时区函数（比如MySQL的CONVERT_TZ），要确保数据库的时区规则是最新的，避免夏令时切换日期计算错误。

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