I am using PostgreSQL and I have a table that tracks when a system is up or down. There are three columns: interval_date with datatype DATE, interval_time_utc with datatype VARCHAR, and status with datatype INTEGER. A status of 0 is up and 1 is down. I am trying to calculate the average uptime on a daily basis. Below is a table example.

So based on the data below:
System was up from 2022-07-05 00:00 to 2022-07-05 18:39 then down from 2022-07-05 18:40 to 2022-07-05 23:59.
System was down from 2022-07-06 00:00 to 2022-07-06 01:14 then the system was up from 2022-07-06 01:15 to 2022-07-06 17:44 then the system was down from 2022-07-06 17:45 to 2022-07-06 23:59.
System was down from 2022-07-07 00:00 to 2022-07-07 03:59 then the system was up from 2022-07-07 04:00 to 2022-07-07 19:59 then the system was down from 2022-07-07 20:00 to 2022-07-07 23:59.
System was down from 2022-07-08 00:00 to 2022-07-08 03:59 then the system was up from 2022-07-08 04:00 to the current UTC time.

Link to fiddle containing schema: https://www.db-fiddle.com/f/v7RCBBNowhv7DmfBoor64X/0

I would expect the following results (assuming my calculations are correct):

CodePudding user response：

Assuming:

• A current version of Postgres.

• A UNIQUE constraint on the timestamp.

• The leading fraction of the first day that is not covered by actual entry is the opposite status of the first entry.

• The trailing fraction of the last day that is not covered by actual entry is the opposite status of the last entry.

• Other than that, every row is allowed to switch on or off (redundantly).

• The result shall contain all days between the first and last entry - including days without any change (all up or all down).

And I converted the table to a sane schema before working with it:

CREATE TABLE fixed AS
SELECT interval_date   interval_time_utc::time AS interval_ts_utc, NOT status::bool AS up
FROM   system_power_history;

Then this does the job:

SELECT the_day, round(extract('epoch' FROM sum(uptime)) / 86400, 2)
FROM  (
   -- add start interval for 1st day (if up)
   SELECT interval_ts_utc::date AS the_day, interval_ts_utc::time::interval AS uptime
   FROM  (SELECT * FROM fixed ORDER BY interval_ts_utc LIMIT 1) sub
   WHERE  NOT up

   UNION ALL
   SELECT the_day, upper(range) - lower(range) AS uptime
   FROM  (
      SELECT the_day::date
           , tsrange(the_day, the_day   interval '1 day') * tsrange(ts, next_ts) AS range  -- intersect with day
      FROM  (
         SELECT interval_ts_utc AS ts
              , lead(interval_ts_utc, 1, date_trunc('day', interval_ts_utc)   interval '1 day')  -- default to start of next day
                     OVER (ORDER BY interval_ts_utc) AS next_ts
              , up
         FROM   fixed
         ) sub
      CROSS  JOIN LATERAL generate_series(date_trunc('day', ts)
                                        , next_ts - interval '1 us'
                                        , interval '1 day') the_day  -- get 1 row per intersecting day
      WHERE  up
      ) sub
   ) sub2
GROUP  BY 1
ORDER  BY 1;

db<>fiddle here

Using the range intersection operator * for convenience.

CodePudding user response：

Here is my take on it - get daily down-time and subtract from 24 hours, except for today, where the subtraction is from current utc time.

select interval_date, 
case when interval_date = current_date then 
round(cast((case when sum(int_time) < 0 then trunc(EXTRACT(EPOCH FROM current_time at time zone 'utc')/60 ) - sum(int_time)
else sum(int_time) end)/trunc(EXTRACT(EPOCH FROM current_time at time zone 'utc')/60 ) as numeric), 2) 
else
round(cast((case when sum(int_time) < 0 then 1440 - sum(int_time)
else sum(int_time) end)/1440 as numeric), 2) 
end up_time
from 
(
select interval_date, interval_time_utc,
case when status = 0 then (-1) * EXTRACT(EPOCH FROM interval_time_utc::time)/60 
else EXTRACT(EPOCH FROM interval_time_utc::time)/60 - 1 end int_time,
status
from system_power_history
  ) t
  group by interval_date
  order by interval_date;

Fiddle here.