Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- original (broken) table:
CREATE TABLE system_power_history (
  interval_date date,
  interval_time_utc varchar(5),
  status integer
);

INSERT INTO system_power_history(interval_date, interval_time_utc, status) VALUES
  ('2022-07-05', '18:40', 1)
, ('2022-07-06', '01:15', 0)
, ('2022-07-06', '17:45', 1)
, ('2022-07-07', '04:00', 0)
, ('2022-07-07', '20:00', 1)
, ('2022-07-08', '04:00', 0)  -- orinal sample values

, ('2022-07-10', '04:00', 0)  -- + some days without switch / without entry
, ('2022-07-12', '04:00', 1)
, ('2022-07-12', '05:00', 0)  -- switch on
, ('2022-07-12', '06:00', 1)  -- and off during same day
, ('2022-07-14', '04:00', 0)
RETURNING *;


interval_date
interval_time_utc
status


2022-07-05
18:40
1

2022-07-06
01:15
0

2022-07-06
17:45
1

2022-07-07
04:00
0

2022-07-07
20:00
1

2022-07-08
04:00
0

2022-07-10
04:00
0

2022-07-12
04:00
1

2022-07-12
05:00
0

2022-07-12
06:00
1

2022-07-14
04:00
0


-- convert to sane table:
CREATE TABLE fixed AS
SELECT interval_date + interval_time_utc::time AS interval_ts_utc, NOT status::bool AS up
FROM   system_power_history;

TABLE fixed;
11 rows affected



interval_ts_utc
up


2022-07-05 18:40:00
f

2022-07-06 01:15:00
t

2022-07-06 17:45:00
f

2022-07-07 04:00:00
t

2022-07-07 20:00:00
f

2022-07-08 04:00:00
t

2022-07-10 04:00:00
t

2022-07-12 04:00:00
f

2022-07-12 05:00:00
t

2022-07-12 06:00:00
f

2022-07-14 04:00:00
t


-- Pure SQL
-- Days with no uptime are missing! Need to do more if you need that ...
SELECT the_day, round(extract('epoch' FROM sum(uptime)) / 86400, 2) AS avg_uptime
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
      WHERE  up
      ) sub
   ) sub2
GROUP  BY 1
ORDER  BY 1;


the_day
avg_uptime


2022-07-05
0.78

2022-07-06
0.69

2022-07-07
0.67

2022-07-08
0.83

2022-07-09
1.00

2022-07-10
1.00

2022-07-11
1.00

2022-07-12
0.21

2022-07-14
0.83


-- BUT a (mostly) procedural solution will be faster in this rare case

-- function to compute uptimes (may be of additional use)
CREATE OR REPLACE FUNCTION f_daily_uptimes()
  RETURNS TABLE (the_day date, uptime interval)
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   _ts   timestamp;
   _ts0  timestamp;
   _up   bool;
   _up0  bool;
BEGIN
   FOR _ts, _up IN
      SELECT interval_ts_utc, up FROM fixed ORDER BY interval_ts_utc
   LOOP
      -- only for 1st iteration
      IF the_day IS NULL THEN
         the_day := _ts::date;
         _ts0 := the_day;
         _up0 = NOT _up;
      END IF;

      LOOP
         IF _up0 THEN
            uptime := LEAST(_ts, the_day + 1) - GREATEST(_ts0, the_day);
            RETURN NEXT;
         ELSIF the_day > _ts0::date AND _ts::date > the_day THEN  -- empty days
            uptime := '0:0';
            RETURN NEXT;
         END IF;

         EXIT WHEN the_day >= _ts::date;
         the_day = the_day + 1;
      END LOOP;
      
      _ts0 := _ts;  -- remember last row
      _up0 := _up;  -- remember last row
   END LOOP;

   -- final interval
   IF _up THEN
      uptime := (the_day + 1) - _ts0;
      RETURN NEXT;
   END IF;
END
$func$;

-- Basic list of uptimes
SELECT * FROM f_daily_uptimes();


the_day
uptime


2022-07-05
18:40:00

2022-07-06
16:30:00

2022-07-07
16:00:00

2022-07-08
20:00:00

2022-07-09
1 day

2022-07-10
04:00:00

2022-07-10
20:00:00

2022-07-11
1 day

2022-07-12
04:00:00

2022-07-12
01:00:00

2022-07-13
00:00:00

2022-07-14
20:00:00


-- your desired result, incl. days with no uptime
SELECT the_day, round(extract('epoch' FROM sum(uptime)) / 86400, 2) AS avg_uptime
FROM   f_daily_uptimes()
GROUP  BY 1
ORDER  BY 1;


the_day
avg_uptime


2022-07-05
0.78

2022-07-06
0.69

2022-07-07
0.67

2022-07-08
0.83

2022-07-09
1.00

2022-07-10
1.00

2022-07-11
1.00

2022-07-12
0.21

2022-07-13
0.00

2022-07-14
0.83


-- Pankaj's query fails for the extended test case:

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;


interval_date
up_time


2022-07-05
0.78

2022-07-06
0.69

2022-07-07
0.67

2022-07-08
1.17

2022-07-10
1.29

2022-07-12
0.21

2022-07-14
1.17

interval_date	interval_time_utc	status
2022-07-05	18:40	1
2022-07-06	01:15	0
2022-07-06	17:45	1
2022-07-07	04:00	0
2022-07-07	20:00	1
2022-07-08	04:00	0
2022-07-10	04:00	0
2022-07-12	04:00	1
2022-07-12	05:00	0
2022-07-12	06:00	1
2022-07-14	04:00	0

interval_ts_utc	up
2022-07-05 18:40:00	f
2022-07-06 01:15:00	t
2022-07-06 17:45:00	f
2022-07-07 04:00:00	t
2022-07-07 20:00:00	f
2022-07-08 04:00:00	t
2022-07-10 04:00:00	t
2022-07-12 04:00:00	f
2022-07-12 05:00:00	t
2022-07-12 06:00:00	f
2022-07-14 04:00:00	t

the_day	avg_uptime
2022-07-05	0.78
2022-07-06	0.69
2022-07-07	0.67
2022-07-08	0.83
2022-07-09	1.00
2022-07-10	1.00
2022-07-11	1.00
2022-07-12	0.21
2022-07-14	0.83

the_day	uptime
2022-07-05	18:40:00
2022-07-06	16:30:00
2022-07-07	16:00:00
2022-07-08	20:00:00
2022-07-09	1 day
2022-07-10	04:00:00
2022-07-10	20:00:00
2022-07-11	1 day
2022-07-12	04:00:00
2022-07-12	01:00:00
2022-07-13	00:00:00
2022-07-14	20:00:00