In this lesson, you will learn how to mathematically detect missing entries in a chronological sequence to ensure your time-series analysis isn't missing critical data points.
The Concept of a "Data Gap"
When dealing with time series, data is rarely perfect. Sensors might fail, or servers might skip a logging interval. A "gap" occurs when the expected interval between two consecutive records is greater than your predefined frequency (e.g., if you expect hourly data, a gap exists if the difference between records is two hours or more).
Detecting Discontinuities
To identify these gaps, we compare each row to the one preceding it. Using the LAG() window function, we can bring the "previous time" into the current row’s context. By calculating the difference between the current time and the previous time, we can filter for instances where that difference exceeds our threshold.
Imagine a table sensor_logs with a column recorded_at. Here is how we detect the exact start of a gap:
WITH time_diffs AS (
SELECT
recorded_at,
LAG(recorded_at) OVER (ORDER BY recorded_at) as prev_time
FROM sensor_logs
)
SELECT
prev_time AS gap_start,
recorded_at AS gap_end
FROM time_diffs
WHERE recorded_at > prev_time + INTERVAL '1 hour';
Why This Matters
If you simply perform an average or a running total on a dataset with gaps, your result will be mathematically skewed. For example, if you are calculating a moving average of temperature, a missing hour shouldn't be treated as a value of zero, nor should it be ignored as if the next record happened immediately after the previous one.
By identifying these discontinuities, you can decide how to handle them: you might choose to impute (fill) the missing values with the last known observation, or simply flag the time ranges for investigation by the data engineering team. Once you know where the sequences are broken, you move from raw data collection to reliable data reporting.
Key Takeaway: Use the LAG() function to compare current and previous timestamps, allowing you to mathematically pinpoint where your time-series sequence is interrupted.