The following codes gives the total duration that a light has been switched on.
CREATE TABLE switch_times (
id SERIAL PRIMARY KEY,
is1 BOOLEAN,
id_dec INTEGER,
label TEXT,
ts TIMESTAMP WITH TIME ZONE default current_timestamp
);
CREATE VIEW makecount AS
SELECT *, row_number() OVER (PARTITION BY id_dec ORDER BY id) AS count
FROM switch_times;
select c1.label, SUM(c2.ts-c1.ts) AS sum
from
(makecount AS c1
inner join
makecount AS c2 ON c2.count = c1.count + 1)
where c2.is1=FALSE AND c1.id_dec = c2.id_dec AND c2.is1 != c1.is1
GROUP BY c1.label;
Link to working demo https://dbfiddle.uk/ZR8pLEBk
Any suggestions on how to alter the code so that it would give the sum over a given specific time period, say the 25th, during which all three lights were switched on for 12 hours? Problem 1: current code gives total sum, as follows. Problem 2: all durations that have not ended are disregarded, because there is no switch off time.
label sum
0x29 MH3 1 day 03:00:00
0x2B MH1 1 day 01:00:00
0x2C MH2 1 day 02:00:00
The expected results is just over a a given date, i.e.
label sum
0x29 MH3 12:00:00
0x2B MH1 12:00:00
0x2C MH2 12:00:00
2
Answers
Assuming the following (which should be defined in the question):
NOT NULL
, you just forgot to declare columns as such.id_dec
and a distinctlabel
. Having both inswitch_times
is redundant. (Normalization!)is1 IS TRUE
. Else it’s considered "off".ts
, not byid
as used in your query (typically incorrect).(id_dec, ts)
. (There is a unique index enforcing that.)tstzrange '[2022-11-25 0:0+02, 2022-11-26 0:0+02)'
(Note the time zone offsets.)Indexes
Have at least these indexes to make everything fast:
Optional step to create table
labels
Why this way? See:
Main query
fiddle
CTE
bounds
is an optional convenience feature to enter lower and upper bound of your time interval once.CTE
snapshot
collects all rows of interest, which consists ofUNION ALL
query)UNION ALL
query)We need to gather 2. separately to cover corner cases where the light was switched on earlier and there is no entry for the given time interval! But we can replace the timestamp to the lower bound immediately.
The final query gets the previous
(is1, ts)
for every row in a subquery, defaulting to "off" if there was no previous row.Finally sum up intervals in the outer
SELECT
. Only sum what’s switched on at the begin (no matter the final state).Related:
My assumption
actual on time is time difference between is1 is true to next is1 false order by ts
Below query will calculate total sum of on time between two dates