skip to Main Content

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


  1. 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:

    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

    1. all rows inside the time interval (1st leg of UNION ALL query)
    2. 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).

    Related:

    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search