Sum over a given time period

Question

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

Please minimize your example, put it also into the question and add the expected result. — S-Man, Nov 28 '22 at 14:15

score 1 · Answer 1 · edited Dec 03 '22 at 08:55

Assuming the following (which should be defined in the question):

Postgres 15.
The table is big, many rows per label, performance matters, we can add indexes.
All columns are actually NOT NULL, you just forgot to declare columns as such.
Evey "light" has a distinct id_dec and a distinct label. Having both in switch_times is redundant. (Normalization!)
A light is "switched on" if the most recent earlier entry has is1 IS TRUE. Else it's considered "off".
The order of rows is established by ts, not by id as used in your query (typically incorrect).
Consecutive entries do not have to change the state.
No duplicate entries for (id_dec, ts). (There is a unique index enforcing that.)
There is no minimum or maximum time interval between entries.
"The 25th" is supposed to mean tstzrange '[2022-11-25 0:0+02, 2022-11-26 0:0+02)' (Note the time zone offsets.)
You want results for all labels that were switched on at all during the given time interval.
There is a table "labels" with one distinct entry per relevant light. If you don't have one, create it.

Indexes

Have at least these indexes to make everything fast:

CREATE INDEX ON switch_times (id_dec, ts DESC);
CREATE INDEX ON switch_times (ts);

Optional step to create table `labels`

CREATE TABLE labels AS
WITH RECURSIVE cte AS (
   (
   SELECT id_dec, label
   FROM   switch_times
   ORDER  BY 1
   LIMIT  1
   )

   UNION ALL
   (
   SELECT s.id_dec, s.label
   FROM   cte c
   JOIN   switch_times s ON s.id_dec > c.id_dec
   ORDER  BY 1
   LIMIT  1
   )
   )
TABLE cte;

ALTER TABLE labels
  ADD PRIMARY KEY (id_dec)
, ALTER COLUMN label SET NOT NULL
, ADD CONSTRAINT label_uni UNIQUE (label)  
;

Why this way? See:

Optimize GROUP BY query to retrieve latest row per user

Main query

WITH bounds(lo, hi) AS (
   SELECT timestamptz '2022-11-25 0:0+02'  -- enter time interval here *once*
        , timestamptz '2022-11-26 0:0+02'
   )
, snapshot AS (
   SELECT id_dec, label, is1, ts
   FROM   switch_times s, bounds b
   WHERE  s.ts >= b.lo
   AND    s.ts <  b.hi

   UNION ALL  -- must be separate   
   SELECT s.*
   FROM   labels l
   JOIN   LATERAL ( -- latest earlier entry 
      SELECT s.id_dec, s.label, s.is1, b.lo AS ts  -- cut off at lower bound
      FROM   switch_times s, bounds b
      WHERE  s.id_dec = l.id_dec
      AND    s.ts < b.lo
      ORDER  BY s.ts DESC
      LIMIT  1
      ) s ON s.is1  -- ... if it's "on"
   )
SELECT label, sum(z - a) AS duration
FROM  (
   SELECT label
        , lag(is1, 1, false) OVER w AS last_is1
        , lag(ts) OVER w AS a
        , ts AS z
   FROM   snapshot
   WINDOW w AS (PARTITION BY label ORDER BY ts ROWS UNBOUNDED PRECEDING)
   ) sub
WHERE  last_is1
GROUP  BY 1;

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 of

all rows inside the time interval (1st leg of UNION ALL query)
the latest earlier row if it was "on" (2nd leg of 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).

Jump SQL gap over specific condition & proper lead() usage

score 0 · Answer 2 · answered Dec 02 '22 at 13:22

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

select
    id_dec ,
    label,
    sum(to_timestamp(nexttime)-ts) as time_def
from
    (
    select
        id_dec,
        "label",
        ts,
        is1,
        case
                when is1 = true then lead(extract(epoch from ts))over(partition by id_dec
        order by
            id_dec ,
                ts asc)
            else 0
        end nexttime
    from
        switch_times
    where
        ts between '2022-11-24' and '2022-11-28'
        ) as a
where
    nexttime <> 0
group by
    id_dec,
    label

Sum over a given time period

2 Answers2

Indexes

Optional step to create table labels

Main query

Optional step to create table `labels`