求助：MySQL基于日期时间查询实现日用电量差值计算（Node-RED场景）

你可以通过两个子查询分别获取当日最新的inputpwr值和昨日最后一条的inputpwr值，再直接计算差值。以下是具体的SQL语句：

基础版（假设date字段是纯日期格式） #

SELECT 
    -- 获取当日最新的总电量
    (SELECT inputpwr FROM pwrtable WHERE date = CURDATE() ORDER BY time DESC LIMIT 1) AS today_latest_pwr,
    -- 获取昨日最后一条的总电量
    (SELECT inputpwr FROM pwrtable WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY time DESC LIMIT 1) AS yesterday_last_pwr,
    -- 计算当日实际用电量
    (SELECT inputpwr FROM pwrtable WHERE date = CURDATE() ORDER BY time DESC LIMIT 1) - 
    (SELECT inputpwr FROM pwrtable WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY time DESC LIMIT 1) AS daily_consumption;

处理空值的增强版 #

如果存在当日还未采集数据，或者昨日无数据的情况，上面的语句会返回NULL。可以用IFNULL处理，避免异常：

SELECT 
    IFNULL(today_latest_pwr, 0) AS today_latest_pwr,
    IFNULL(yesterday_last_pwr, 0) AS yesterday_last_pwr,
    IFNULL(today_latest_pwr, 0) - IFNULL(yesterday_last_pwr, 0) AS daily_consumption
FROM (
    SELECT 
        (SELECT inputpwr FROM pwrtable WHERE date = CURDATE() ORDER BY time DESC LIMIT 1) AS today_latest_pwr,
        (SELECT inputpwr FROM pwrtable WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY time DESC LIMIT 1) AS yesterday_last_pwr
) AS temp_table;

如果date字段是datetime类型（包含日期+时间） #

如果你的date字段实际是datetime格式（比如2024-05-20 14:30:00），需要用DATE()函数提取日期部分，调整语句如下：

SELECT 
    (SELECT inputpwr FROM pwrtable WHERE DATE(date) = CURDATE() ORDER BY date DESC LIMIT 1) AS today_latest_pwr,
    (SELECT inputpwr FROM pwrtable WHERE DATE(date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY date DESC LIMIT 1) AS yesterday_last_pwr,
    (SELECT inputpwr FROM pwrtable WHERE DATE(date) = CURDATE() ORDER BY date DESC LIMIT 1) - 
    (SELECT inputpwr FROM pwrtable WHERE DATE(date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY date DESC LIMIT 1) AS daily_consumption;

关键说明 #

• CURDATE()：获取当前系统日期（比如2024-05-20）
• DATE_SUB(CURDATE(), INTERVAL 1 DAY)：获取前一天的日期
• ORDER BY time DESC LIMIT 1：按时间倒序排序，取最新的一条数据

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