UPDATE to subtract downtime per hour

Question

I have 2 tables.

The dummy table:

and the down_event table:

availability_time in the dummy table ranges from 00:00:00 to 23:00:00. Every down_duration_sec in the down_event table shall be subtracted from duration_sec in the corresponding row of the dummy table. An event occurring at down_time 03:59:59 only applies to the row with availability_time 03:00:00 in the dummy table.

The example above only shows rows for 2020-02-25, but there can be many dates for the same terminal_id.

To be clear, assuming these rows in table dummy:

terminal_id    availability_date   availability_time   duration_sec
___________    _________________   _________________   ____________
02262261       2020-02-25          00:00:00            3600
02262261       2020-02-25          01:00:00            3600
02262261       2020-02-25          02:00:00            3600
02262261       2020-02-25          03:00:00            3600

For an event occurring on the date 2020-02-25 at the time 1:43:23 with a down_duration_sec of 10 seconds, the dummy table shall be updated to:

terminal_id    availability_date   availability_time   duration_sec
___________    _________________   _________________   ____________
02262261       2020-02-25          00:00:00            3600
02262261       2020-02-25          01:00:00            3590
02262261       2020-02-25          02:00:00            3600
02262261       2020-02-25          03:00:00            3600

So far this is my query code:

    UPDATE dashboard.dummy 
SET duration_sec = 3600 - t.down_duration_sec
FROM (  
    SELECT down_duration_sec
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
    GROUP BY down_duration_sec
) t 
WHERE t.terminal_id = dashboard.dummy.terminal_id
AND availability_date IN (
    SELECT down_date 
    FROM dashboard.down_event 
    WHERE down_date IN ( SELECT availability_date FROM dashboard.dummy)
)

This query works and was able to populate the terminal_id, availability_date and availability_time.

But once I add this code to subtract the duration_sec:

AND availability_time IN(
    SELECT terminal_id, down_time
    FROM dashboard.down_event 
    WHERE down_time between
    (
        SELECT terminal_id, availability_time 
        FROM dashboard.dummy 
        WHERE terminal_id IN (SELECT terminal_id FROM dashboard.down_event)
        GROUP BY terminal_id, availability_time
    ) 
    AND
    (
        SELECT availability_time + interval '1 hour'
        FROM dashboard.dummy
        WHERE terminal_id IN (SELECT terminal_id FROM dashboard.down_event)
        GROUP BY availability_time
    )
)

.. I get the error:

subquery must return only one column

How do I get the subtraction right?

This can't work since you're returning 2 columns. `AND availability_time IN (SELECT terminal_id, down_time`. Might work better with JOINs, I'd have to set this up locally and dig in. — mike.k, Apr 18 '20 at 21:19
I took the liberty to clarify as best as I understood after reading the question a couple of times. Please adjust if I didn't get it right. Note that plain text is very much preferred over images, and always declare your version of Postgres. — Erwin Brandstetter, Apr 20 '20 at 01:47

score 1 · Accepted Answer · answered Apr 20 '20 at 00:53

Assuming all columns to be NOT NULL.

UPDATE dashboard.dummy d
SET    duration_sec = 3600 - t.sum_down
FROM  (
   SELECT terminal_id, down_date, date_trunc('hour', down_time) AS down_time
        , sum(down_duration_sec) AS sum_down
   FROM   dashboard.down_event 
   GROUP  BY 1, 2, 3
   ) t
WHERE  (t.terminal_id, t.down_date        , t.down_time)
     = (d.terminal_id, d.availability_date, d.availability_time)

In subquery t, aggregate multiple downtimes per hour (if that's possible?) after truncating to the hour with date_trunc(). Then join to it in a FROM clause. This only updates rows in the dummy table that actually need an update (should be very few) - as opposed to the subselect demonstrated by George, which updates every row (lots of empty updates). Massively cheaper. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

The expression date_trunc('hour', down_time) produces an interval, but the comparison to time still works.

Aside: seems odd to subtract downtime from the starting hour instead of actually affected hours.

George S · Answer 2 · 2020-04-19T14:28:55.683

0

The most straightforward way to do this is with a subselect in the update statement, e.g.:

update dummy d set duration_sec = coalesce( 
  (select 3600 - sum(de.down_duration_sec)
   from down_event de
   where date_trunc('hour', de.down_date + de.down_time) = date_trunc('hour',d.availability_date + d.availability_time)
    and d.terminal_id = de.terminal_id
 ),3600) ;

edited Apr 19 '20 at 14:28

answered Apr 19 '20 at 04:13

George S

2,041
9
13

score 0 · Answer 3 · answered Apr 20 '20 at 01:47

Thank you all for answering.

I fixed this problem by using this query:

UPDATE dashboard.dummy 
SET duration_sec = 3600 - t.down_duration_sec
FROM (  
    SELECT down_duration_sec, down_date, terminal_id, down_time
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
    GROUP BY down_duration_sec, down_date, terminal_id, down_time
) t 
WHERE t.terminal_id = dashboard.dummy.terminal_id
AND dashboard.dummy.availability_date = t.down_date
AND t.down_time BETWEEN dashboard.dummy.availability_time AND dashboard.dummy.availability_time + interval '1 hour'

I just used the BETWEEN AND of the query.

`GROUP BY down_duration_sec, down_date, terminal_id, down_time` seems like noise? (Unless there can be complete duplicates and you only want to use each set once?) OTOH, can you rule out multiple *distinct* downtimes for the same hour? If that can happen, this query breaks because you cannot update the same row multiple times in the same `UPDATE`. Plus, `BETWEEN` is corner-case wrong the way you have it, since it *includes* lower and upper bound, while you want to *exclude* the start of the next hour. — Erwin Brandstetter, Apr 20 '20 at 01:54

UPDATE to subtract downtime per hour

3 Answers3