CODEWITHSUNDEEP
sqlpostgresql
Derp derp
Derp derp

Reputation: 209

Postgres how to determine there are X records spanning 2 days of datetimes in a table

I have a table containing electricity meter readings which looks something like this:

| meter_id | reading_interval_datetime |
| 110      | 2018-01-15T00:00:00+00:00 |
| 110      | 2018-01-15T00:30:00+00:00 |

The table is filled with at most 48 records per day (one reading every 30 mins).

What's an efficient way to check if a particular meter has at least two days of readings in there?

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can determine if a meter_id has at least two days by doing:

select meter_id
from t
group by meter_id
having min(reading_interval_datetime::date) <> max(reading_interval_datetime::date);

This will check that there are two dates in the data.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181380

I would do this:

sql> create index your_table_idx on your_table(meter_id, date(reading_interval_datetime));
sql> select meter_id, date(reading_interval_datetime), count(1)
       from your_table
      where meter_id = THE_METER_ID_YOUD_LIKE_TO_CHECK
      group by meter_id, date(reading_interval_datetime)
     having count(1) > 1

Upvotes: 0

Related Questions