如何通过SQL将设备停留时间段拆分为每5分钟时间点记录？

核心思路 #

要实现需求，需要把每个设备的停留时间段拆分为5分钟整间隔的时间点（如XX:00、XX:05、XX:10），筛选出落在设备在房间时间段内的时间点，再关联设备和房间信息。

针对PostgreSQL的SQL实现 #

假设你的objects表中start_time和end_time是字符串格式（如31.10.2022 10:05:00），可以用以下查询：

SELECT
  o.modellnumber AS modell,
  o.roomnumber AS room,
  TO_CHAR(gs.time_point, 'DD.MM.YYYY HH24:MI:SS') AS time
FROM
  objects o
CROSS JOIN LATERAL
  generate_series(
    -- 计算符合要求的起始5分钟时间点
    CASE
      WHEN date_trunc('minute', to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) 
           - (EXTRACT(minute FROM to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) %5)*interval '1 minute' 
           < to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')
      THEN date_trunc('minute', to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) 
           - (EXTRACT(minute FROM to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) %5)*interval '1 minute' 
           + interval '5 minutes'
      ELSE date_trunc('minute', to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) 
           - (EXTRACT(minute FROM to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')) %5)*interval '1 minute'
    END,
    to_timestamp(o.end_time, 'DD.MM.YYYY HH24:MI:SS'),
    interval '5 minutes'
  ) gs(time_point)
ORDER BY
  modell, room, gs.time_point;

代码解释

• to_timestamp(o.start_time, 'DD.MM.YYYY HH24:MI:SS')：把字符串时间转成数据库可计算的timestamp类型，指定格式匹配你的数据（日.月.年 时:分:秒）。
• date_trunc('minute', ...)：将时间截断到分钟级，配合%5计算最近的5分钟整时间点。
• CASE语句：确保起始时间点不早于设备进入房间的时间，避免出现设备还没进入就记录的情况。
• generate_series：生成从起始点到设备离开时间的5分钟间隔时间序列。
• TO_CHAR(...)：把timestamp类型的时间点转回你需要的字符串格式。

针对MySQL的SQL实现 #

MySQL没有generate_series函数，需要用递归CTE生成时间序列：

WITH RECURSIVE time_series AS (
  SELECT
    o.modellnumber AS modell,
    o.roomnumber AS room,
    -- 计算起始5分钟时间点
    CASE
      WHEN DATE_FORMAT(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'), '%Y-%m-%d %H:00:00') 
           + INTERVAL FLOOR(MINUTE(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'))/5)*5 MINUTE 
           < STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s')
      THEN DATE_FORMAT(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'), '%Y-%m-%d %H:00:00') 
           + INTERVAL FLOOR(MINUTE(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'))/5)*5 MINUTE 
           + INTERVAL 5 MINUTE
      ELSE DATE_FORMAT(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'), '%Y-%m-%d %H:00:00') 
           + INTERVAL FLOOR(MINUTE(STR_TO_DATE(o.start_time, '%d.%m.%Y %H:%i:%s'))/5)*5 MINUTE
    END AS time_point,
    STR_TO_DATE(o.end_time, '%d.%m.%Y %H:%i:%s') AS end_time
  FROM objects o
  UNION ALL
  SELECT
    modell,
    room,
    time_point + INTERVAL 5 MINUTE,
    end_time
  FROM time_series
  WHERE time_point + INTERVAL 5 MINUTE <= end_time
)
SELECT 
  modell, 
  room, 
  DATE_FORMAT(time_point, '%d.%m.%Y %H:%i:%s') AS time
FROM time_series
ORDER BY modell, room, time_point;

注意事项 #

• 确保时间格式参数和你的数据完全匹配，比如如果你的时间是YYYY-MM-DD格式，要调整to_timestamp或STR_TO_DATE里的格式字符串。
• 如果你的时间字段已经是timestamp/datetime类型，可以去掉字符串转时间的步骤，直接使用字段本身。

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