CODEWITHSUNDEEP
sqlpostgresql
Tom
Tom

Reputation: 8012

PostgreSQL - Query to show which time intervals contain data

I have a table that contains timestamped data. I'm trying to construct a query that will return one row for each 5-minute interval, with a column which is TRUE if a timestamp falls in that interval and FALSE if none do, starting at the earliest timestamp in the data and ending at the latest.

I can get the 5-minute intervals containing timestamps like this:

SELECT DISTINCT DATE_TRUNC('hour', t) + ( EXTRACT(minute FROM t)::int / 5 * interval '5 min' ) AS t,
       TRUE AS val
FROM data_table
ORDER BY t;

But how can I then fill this in with the intervals containing no data?

Upvotes: 0

Views: 516

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

I need a function to round a timestamp down to the previous 5 minute boundary:

CREATE OR REPLACE FUNCTION trunc_five_min(timestamp with time zone)
   RETURNS timestamp with time zone
   LANGUAGE sql STABLE AS
$$SELECT date_trunc('hour', $1)
         + date_trunc(
              'minute',
              ($1 - date_trunc('hour', $1)) / 5
           ) * 5$$;

This function will get inlined into the query, so you can see it as a notational shortcut.

When I have a time series table like this:

TABLE samples ORDER BY ts;

┌────────────────────────┐
│           ts           │
├────────────────────────┤
│ 2017-06-02 11:58:30+02 │
│ 2017-06-02 12:00:00+02 │
│ 2017-06-02 12:03:00+02 │
│ 2017-06-02 12:17:00+02 │
│ 2017-06-02 12:17:22+02 │
└────────────────────────┘
(5 rows)

I can produce the desired result with this query:

WITH lim AS (SELECT trunc_five_min(min(ts)) AS min,
                    trunc_five_min(max(ts)) AS max
             FROM samples)
SELECT i.i AS interval_start,
       count(s.ts) AS num_samples
FROM lim
   CROSS JOIN LATERAL generate_series(lim.min, lim.max, INTERVAL '5 minutes') i
   LEFT JOIN samples s
      ON s.ts >= i AND s.ts < i + INTERVAL '5 minutes'
GROUP BY i.i ORDER BY i.i;

┌────────────────────────┬─────────────┐
│     interval_start     │ num_samples │
├────────────────────────┼─────────────┤
│ 2017-06-02 11:55:00+02 │           1 │
│ 2017-06-02 12:00:00+02 │           2 │
│ 2017-06-02 12:05:00+02 │           0 │
│ 2017-06-02 12:10:00+02 │           0 │
│ 2017-06-02 12:15:00+02 │           2 │
└────────────────────────┴─────────────┘
(5 rows)

Upvotes: 2

Related Questions