CODEWITHSUNDEEP
postgresql
Defozo
Defozo

Reputation: 3092

How to check if there's a record in every hour in specified time-frame and then count it?

I'm using PostgreSQL and this is my table measurement_archive:

+-----------+------------------------+------+-------+
| sensor_id | time                   | type | value |
+-----------+------------------------+------+-------+
| 123       | 2017-11-26 01:53:11+00 | PM25 | 34.32 |
+-----------+------------------------+------+-------+
| 123       | 2017-11-26 02:15:11+00 | PM25 | 32.1  |
+-----------+------------------------+------+-------+
| 123       | 2017-11-26 04:32:11+00 | PM25 | 75.3  |
+-----------+------------------------+------+-------+

I need a query that will take records from specified timeframe (eg. from 2017-01-01 00:00:00 to 2017-12-01 23:59:59) and then check if in every hour there is at least 1 record - if there is, then add 1 to result.

So, if I make that query from 2017-11-26 01:00:00 to 2017-11-26 04:59:59+00 for sensor_id == 123 on above table then the result should be 3.

Upvotes: 0

Views: 61

Answers (2)

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

alternative solution would be using distinct,

select count(*) from (select distinct a, extract(hour from time) from t where time >'2017-11-26 01:00:11' and time <'2017-11-26 05:00:00' and sensor_id=123)t;

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125464

select count(*)
from (
    select date_trunc('hour', time) as time
    from measurement_archive
    where
        time >= '2017-11-26 01:00:00' and time < '2017-11-26 05:00:00'
        and
        sensor_id = 123
    group by 1
) s

Upvotes: 1

Related Questions