Question 113670 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query: how to calculate the sum of durations for a specific event?

I’m using Node Red and Postgresql to record events, one event is that a specific 0x40 light has been switched on, another event is that the same light has been switched off. For simplicity, the on and off times have been stored in different tables.

Using SQL, is it possible to calculate the sum of durations that each light has been on during a given time period, e.g. a 24 hour period? As such the information regarding switch on switch off times is not very useful, but once aggregated the information becomes much more useful.

The table below shows that the light 0x40 was switched on for about 4 seconds, so here the desired output would be as follows.

iddec  label            TimeOnInSeconds

64    0x40 ruokapöytä   4

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on the provided example, I made an variation of the code. Seems to do what is should. Please see working demo here.

    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;

  2. 0

    I do not know where you got the idea for simplicity, the on and off times have been stored in different tables. This simplicity makes this query much more difficult, and probably less stable as well. This added complexity extends to all queries needing both times. But, still doable:

    with switchset (swon, iddec, label, ts) as
     ( select 'on', iddec, label, date_trunc('second',ts) from switchon
       union all 
       select 'off', iddec, label, date_trunc('second', ts)  from switchoff
        order by 2, 4, 1
     ) -- select * from switchset 
select iddec, label,"Duration HH:MI:SS","Time in Seconds" 
  from ( select t.*
              , ts - lag(ts) over (partition by label order by ts) "Duration HH:MI:SS"
              , extract('second' from (ts - lag(ts) over (partition by label order by ts nulls last)))::integer "Time in Seconds"
           from switchset t
       ) dur
   where swon = 'off';

    Now what does it take going through the difficult process of inserting a row at switch on and updating that same row at switch off:

    select iddec, label
     , date_trunc('second',off_ts) - date_trunc('second',on_ts) "Duration HH:MI:SS"
     , extract('second' from (date_trunc('second',off_ts) - date_trunc('second',on_ts)))::integer "Time in Seconds" 
  from switchset;

    See demo here which demonstrates both. In both date_trunc('second',timestamp) eliminates fractional seconds which you do not seem to want.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search