SQL Query for "Usage" in 15 minutes intervals

Question

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.

Answer 1

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