CODEWITHSUNDEEP
sqlpostgresqltimescaledb
Raphiki
Raphiki

Reputation: 190

Timescaledb time_bucket fetch periods with no row

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 :)

Upvotes: 5

Views: 3277

Answers (2)

suntruth
suntruth

Reputation: 351

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

Upvotes: 3

Raphiki
Raphiki

Reputation: 190

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.

Upvotes: 1

Related Questions