Timescaledb time_bucket fetch periods with no row

Question

I have sensors emitting data every ~20 seconds. However sometimes these are in trouble and doesn't emit data for minutes or hours.

I would like to fetch those disconnection and know how long these last.

So I tried with the function time_bucket to count number of data by bucket of 5 minutes (for example), but this function only affect the data itself. So it isn't possible to fetch bucket HAVING COUNT(*) = 0.

I tried this :

SELECT time_bucket('5 minutes', datetime) AS bucket, COUNT(*) AS nb_datas
FROM measures
WHERE id_sensor = 123456
GROUP BY bucket
HAVING COUNT(*) = 0
ORDER BY bucket DESC;

But logically it return nothing.

A little help would be apprecied :)

score 3 · Accepted Answer · answered Jan 09 '18 at 17:39

3

You may be interested in gap filling, which is described in the TimescaleDB docs at: http://docs.timescale.com/using-timescaledb/reading-data#gap-filling

answered Jan 09 '18 at 17:39

suntruth

341
1
3

score 1 · Answer 2 · answered Jan 09 '18 at 09:15

Here the solution I found thanks to TimescaleDB github :

SELECT period.date
FROM (
    SELECT generate_series(date '2018-01-09 00:00:00', now(), interval '5 minutes') date
) as period
WHERE period.date NOT IN (
    SELECT
        time_bucket('5 minutes', datetime) AS date
    FROM measures
    WHERE id_sensor = '123456'
        AND datetime >= '2018-01-09 00:00:00'
    GROUP BY date
    ORDER BY date
)

It use generate_series function from PostgreSQL to fill buckets, then substract buckets with datas to leave only those HAVING COUNT(*) = 0.

Timescaledb time_bucket fetch periods with no row

2 Answers2