Postgresql - SQL Query for "Usage" in 15 minutes intervals

user39063
May 14, 2023
199 views
0 votes
2 Answers

I have a digital Meter. This digital Meter puts it’s metering Data into a (Postgres) Database. The relevant Data looks like this:

timestamp, total_m3

Every few seconds to minutes, I get the Data logged into the Database. So every 15 Minutes I have 0 to 100 entries in the Database.

The total_m3 Value is the incrementing meter value.

I’m looking for a select query to get:
Every 15 Minutes how much the meter was measuring.

For example:

2023-05-11 20:59:49.000000, 296.650
2023-05-11 20:50:49.000000, 296.650
2023-05-11 20:45:49.000000, 296.250
2023-05-11 20:37:49.000000, 296.150
2023-05-11 20:30:49.000000, 296.000

The Result should look like:

2023-05-11 21:00:00.000000, 0.4
2023-05-11 20:45:00.000000, 0.25

Would it be possible to do that in SQL? Or should I just say F* it, I code the logic into the App.

Answers

- GMB
- May 11, 2023 at 9:01 pm
- 0 votes
0
If you are running Postgres 14 or higher, you can perform the 15 minutes truncation with date function date_bin().

It is not obvious how you want to compute the meter difference.

If you are looking for the overall increase within the interval, the difference between the min and max values should give you the result you want:
```
select date_bin('15 minutes', ts, timestamp '2000-01-01') ts_15_min,
    max(total_m3) - min(total_m3) meter_increase
from mytable
group by 1
order by 1
```
Or maybe you want to compare the last value of the previous interval to the last value of the current interval. If so, we can use window functions:
```
select date_bin('15 minutes', ts, timestamp '2000-01-01') ts_15_min,
    max(total_m3) 
    - lag(max(total_m3), 1, min(total_m3)) over(order by date_bin('15 minutes', ts, timestamp '2000-01-01')) meter_increase
from mytable
group by 1
order by 1
```
Both queries assume that the meter value is always increasing, as explained in the question.

Note that I renamed the timestamp column to ts, so it does not clash with the corresponding SQL keyword.
Login or Signup to reply.

- nbk
- May 11, 2023 at 9:04 pm
- 0 votes
0
You can parse the date as intervall of 15 Minutes and use that to group by
```
SELECT
    date_trunc('minute', "dt") + interval '15 minute'
  - (CAST(EXTRACT(MINUTE FROM "dt") AS integer) % 15) * interval '1 minute' AS trunc_15_minute


  ,MAX("value") - MIN("value") dff
FROM tabl1
GROUP BY trunc_15_minute
ORDER BY trunc_15_minute;
```
trunc_15_minute dff

2023-05-11 20:45:00 0.150

2023-05-11 21:00:00 0.400
```
SELECT 2
```
fiddle
Login or Signup to reply.