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.
2
Answers
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:
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:
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.You can parse the date as intervall of 15 Minutes and use that to group by
fiddle