MySQL求助：按电表箱分组，获取2AM-2AM时段读数最值

问题分析 #

你的思路方向是对的——通过把时间提前2小时，将当日2AM至次日2AM的时段映射为前一天0AM至当天0AM的自然日区间，但原代码重复调用时间计算表达式，且拆分年月日分组的方式不够简洁，容易在跨年月的边缘场景出问题。更可靠的方式是先预计算调整后的日期标识，再基于它分组。

修正后的SQL代码 #

方法1：子查询预计算（兼容所有MySQL版本）

SELECT 
    power_box,
    MAX(reading) AS max_reading,
    MIN(reading) AS min_reading,
    -- 可选：显示对应时段的起止时间
    CONCAT(DATE_FORMAT(adjusted_date, '%Y-%m-%d'), ' 02:00:00') AS period_start,
    CONCAT(DATE_FORMAT(DATE_ADD(adjusted_date, INTERVAL 1 DAY), '%Y-%m-%d'), ' 02:00:00') AS period_end
FROM (
    SELECT 
        power_box,
        reading,
        -- 把毫秒时间戳转成datetime后减2小时，提取日期作为分组标识
        DATE(DATE_ADD(FROM_UNIXTIME(eventTime/1000), INTERVAL -2 HOUR)) AS adjusted_date
    FROM meters
) AS temp
GROUP BY adjusted_date, power_box;

方法2：CTE写法（MySQL 8.0及以上版本）

WITH temp_meters AS (
    SELECT 
        power_box,
        reading,
        DATE(DATE_ADD(FROM_UNIXTIME(eventTime/1000), INTERVAL -2 HOUR)) AS adjusted_date
    FROM meters
)
SELECT 
    power_box,
    MAX(reading) AS max_reading,
    MIN(reading) AS min_reading,
    CONCAT(DATE_FORMAT(adjusted_date, '%Y-%m-%d'), ' 02:00:00') AS period_start,
    CONCAT(DATE_FORMAT(DATE_ADD(adjusted_date, INTERVAL 1 DAY), '%Y-%m-%d'), ' 02:00:00') AS period_end
FROM temp_meters
GROUP BY adjusted_date, power_box;

逻辑验证 #

举两个典型时间点的映射例子：

• 原时间2024-05-20 02:00:00：减2小时后是2024-05-20 00:00:00，提取日期为2024-05-20，对应时段是2024-05-20 02:00:00至2024-05-21 02:00:00
• 原时间2024-05-21 01:59:59：减2小时后是2024-05-20 23:59:59，提取日期仍为2024-05-20，归属到上述同一时段，完全符合你的需求

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