Find missing datapoints in time sequence

Question

I have datapoints with 15 minutes

counting_point        time.                           value
AT100                 2024-01-03 23:00:00.000000000.  100
AT100                 2024-01-03 23:15:00.000000000.  120
AT100                 2024-01-03 23:45:00.000000000.  122

Now i have the problem that the datapoint with time 2024-01-03 23:30:00.000000000 is missing.

How can i write a sql query to detect this missing fields? There can be multiple missing fields

Answer 1

Each time there is an operation to be scheduled on a regular basis (every day or every hour or every 15 minutes...) it is necessary that your data model contain a planning table with all the point entries over time without discontinuities.

As an example, I create the following table :

CREATE TABLE T_SCHEDULE_SCH (SCH_TIME TIMESTAMP PRIMARY KEY);
WITH 
T AS (SELECT CAST('2024-01-01' AS TIMESTAMP) AS PIT
      UNION ALL 
      SELECT PIT + INTERVAL '15' MINUTE 
      FROM   T
      WHERE  PIT < '2033-12-31 23:59:00')
SELECT *
FROM   T;

Then the final query is trivial...

SELECT PIT
FROM   T_SCHEDULE_SCH 
EXCEPT
SELECT "time" 
FROM   datapoints;