如何检测SQL表中秒级时间数据是否存在缺失？

Hey there! Let's work through how to detect missing second-level timestamps in your table. Since your data is logged every second, here are a couple of practical, database-friendly approaches to check for gaps:

This method generates every possible second between your earliest and latest timestamp, then checks which ones don't exist in your table. It's great if you want to see exactly which seconds are missing.

Step 1: First, get your time range bounds #

SELECT MIN(sDateTime) AS earliest_time, MAX(sDateTime) AS latest_time FROM your_table;

Step 2: Generate a full second-by-second sequence & find gaps #

The exact syntax varies by database:

• MySQL 8.0+ (using Recursive CTE)

  WITH RECURSIVE full_time_series AS (
      SELECT MIN(sDateTime) AS current_sec FROM your_table
      UNION ALL
      SELECT DATE_ADD(current_sec, INTERVAL 1 SECOND) 
      FROM full_time_series
      WHERE current_sec < (SELECT MAX(sDateTime) FROM your_table)
  )
  SELECT current_sec AS missing_second
  FROM full_time_series
  LEFT JOIN your_table ON full_time_series.current_sec = your_table.sDateTime
  WHERE your_table.sDateTime IS NULL;
  

• PostgreSQL (using generate_series)

  SELECT generate_series(
      (SELECT MIN(sDateTime) FROM your_table),
      (SELECT MAX(sDateTime) FROM your_table),
      '1 second'::interval
  ) AS missing_second
  EXCEPT
  SELECT sDateTime FROM your_table;
  

If you just want to confirm whether gaps exist (and identify the ranges where they occur), you can calculate the time difference between consecutive rows for each sItem.

Using Window Functions (Modern Databases: MySQL 8+, PostgreSQL, SQL Server, etc.) #

This uses LAG() to grab the previous timestamp for each item, then checks if the gap is larger than 1 second:

SELECT
    sItem,
    LAG(sDateTime) OVER (PARTITION BY sItem ORDER BY sDateTime) AS previous_timestamp,
    sDateTime AS current_timestamp,
    EXTRACT(EPOCH FROM (sDateTime - LAG(sDateTime) OVER (PARTITION BY sItem ORDER BY sDateTime))) AS seconds_between
FROM your_table
WHERE EXTRACT(EPOCH FROM (sDateTime - LAG(sDateTime) OVER (PARTITION BY sItem ORDER BY sDateTime))) > 1
ORDER BY sItem, current_timestamp;

Results will show you pairs of timestamps where the gap exceeds 1 second—meaning all seconds between previous_timestamp + 1 and current_timestamp - 1 are missing.

For Older Databases (No Window Functions) #

Use a self-join to find adjacent rows with no other entries in between, then check the time gap:

SELECT
    t1.sItem,
    t1.sDateTime AS previous_timestamp,
    t2.sDateTime AS current_timestamp,
    TIMESTAMPDIFF(SECOND, t1.sDateTime, t2.sDateTime) AS seconds_between
FROM your_table t1
JOIN your_table t2 
    ON t1.sItem = t2.sItem 
    AND t2.sDateTime > t1.sDateTime
WHERE NOT EXISTS (
    SELECT 1 FROM your_table t3
    WHERE t3.sItem = t1.sItem 
      AND t3.sDateTime > t1.sDateTime 
      AND t3.sDateTime < t2.sDateTime
)
AND TIMESTAMPDIFF(SECOND, t1.sDateTime, t2.sDateTime) > 1
ORDER BY t1.sItem, t1.sDateTime;

Quick Notes #

• If your data is split across multiple sItem values, make sure to partition/group by sItem—each item's timestamp sequence is independent!
• Approach 1 gives you the exact missing seconds, which is useful for auditing. Approach 2 is faster for large datasets if you just need to confirm gaps exist.

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