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?

Answer 1

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.

Answer 2

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.

Answer 3

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) ;