如何通过SQL在MySQL中基于起止时间计算PV发电量的差值

这问题其实挺常见的，毕竟累计发电量的差值就是你要的时段发电量嘛！我给你分两种场景来实现，覆盖精确匹配和非精确匹配的情况：

场景1：指定的起止时间刚好存在数据记录 #

如果用户输入的起止时间正好是数据库里5分钟间隔的记录时间，直接取两个时间点的数值相减就行，最简单的写法用子查询：

SELECT
    -- 取结束时间（较晚的时间点）的累计发电量
    (SELECT value 
     FROM your_table
     WHERE date = '2021-10-18 16:45:00' 
       AND metric = 'pv_generation_energy')
    -
    -- 取起始时间（较早的时间点）的累计发电量
    (SELECT value 
     FROM your_table
     WHERE date = '2021-10-18 16:30:00' 
       AND metric = 'pv_generation_energy') AS period_total_generation;

你也可以用JOIN的方式实现，效果完全一样：

SELECT t_end.value - t_start.value AS period_total_generation
FROM
    (SELECT value FROM your_table
     WHERE date = '2021-10-18 16:45:00' AND metric = 'pv_generation_energy') t_end,
    (SELECT value FROM your_table
     WHERE date = '2021-10-18 16:30:00' AND metric = 'pv_generation_energy') t_start;

场景2：指定的起止时间没有对应的记录（比如不是整5分的时间） #

如果用户输入的时间刚好不在5分钟的间隔点上，我们需要取离指定时间最近的有效记录来计算。比如取不晚于结束时间的最新记录，减去不早于起始时间的最早记录：

SELECT
    COALESCE(
        (SELECT value 
         FROM your_table
         WHERE date <= '2021-10-18 16:47:00'  -- 假设用户输入的结束时间不是整5分
           AND metric = 'pv_generation_energy'
         ORDER BY date DESC LIMIT 1), 0)
    -
    COALESCE(
        (SELECT value 
         FROM your_table
         WHERE date >= '2021-10-18 16:28:00'  -- 假设用户输入的起始时间不是整5分
           AND metric = 'pv_generation_energy'
         ORDER BY date ASC LIMIT 1), 0) AS period_total_generation;

这里用COALESCE是为了避免某个时间点找不到数据时结果变成NULL，找不到的话就用0来替代，你也可以根据实际需求调整这个默认值。

关键注意点 #

• 一定要过滤metric字段：如果以后数据库里添加了其他指标，这个过滤能避免取错数据。
• 确认时间字段的类型：如果date字段是字符串类型，要确保格式匹配；如果是DATETIME类型，直接用时间字符串查询就没问题。
• 累计值的趋势：因为你的数据是累计发电量，所以较晚时间点的数值一定大于等于较早时间点的数值，所以结果不会出现负数（除非数据有异常）。

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