如何计算MySQL传感器日志表中OFF与ON动作记录的时间间隔

这个需求完全可以实现，以下是两种常用的落地方式，先统一约定表名为sensor_log，建议将时间（Time）字段设置为DATETIME或TIMESTAMP类型方便计算时间差：

方案1：使用MySQL触发器（全自动实现，无需修改业务插入代码） #

创建BEFORE INSERT触发器，新增记录时自动判断动作类型并计算时长，代码如下：

DELIMITER //
CREATE TRIGGER calc_duration_before_insert
BEFORE INSERT ON sensor_log
FOR EACH ROW
BEGIN
  -- 仅当新增动作是ON时才计算时长
  IF NEW.Action = 'ON' THEN
    -- 查询当前传感器最近一次OFF动作的时间
    SELECT MAX(`Time`) INTO @last_off_time 
    FROM sensor_log 
    WHERE Sensor = NEW.Sensor AND Action = 'OFF';
    -- 计算时间差，这里示例单位为秒，可按需修改TIMESTAMPDIFF的第一个参数（HOUR/MINUTE等）
    IF @last_off_time IS NOT NULL THEN
      SET NEW.Duration = TIMESTAMPDIFF(SECOND, @last_off_time, NEW.`Time`);
    ELSE
      -- 无对应上一次OFF记录时设为NULL
      SET NEW.Duration = NULL;
    END IF;
  END IF;
END //
DELIMITER ;

方案2：插入语句自带计算逻辑（适合不想使用触发器的场景） #

插入ON类型记录时直接通过INSERT ... SELECT语法同步计算时长，示例代码如下：

INSERT INTO sensor_log (`Time`, Action, Sensor, Duration)
SELECT 
  '2024-05-20 18:14:00' AS insert_time, -- 替换为实际插入的时间
  'ON' AS insert_action,
  '001' AS insert_sensor,
  IF(MAX(`Time`) IS NOT NULL, TIMESTAMPDIFF(SECOND, MAX(`Time`), '2024-05-20 18:14:00'), NULL) AS calc_duration
FROM sensor_log
WHERE Sensor = '001' AND Action = 'OFF';

注意事项 #

• 建议创建联合索引idx_sensor_action_time(Sensor, Action, Time)，大幅提升查询上一次OFF时间的性能，数据量较大时也不会出现插入卡顿
• 如果业务存在连续插入多条ON记录的场景，可以额外增加判断逻辑，比对上一次动作类型，避免逻辑异常
• 若需要Duration存储为时分秒格式，可将计算结果用SEC_TO_TIME()函数转换即可

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