Time interval postgres

Question

I have a table which contains id (unique), system id, timestamp and status. When status 1 it means, system unavailable, when 0 - available:

CREATE temp TABLE temp_data_test (
       id int8 NULL,
       system_id int8 NULL,
       time timestamptz NULL,
       status int4 NULL
);
 
INSERT INTO temp_data_test (id, system, time, status) VALUES
       (53,1,'2022-04-02 13:57:07.000',1),
       (54,1,'2022-04-02 14:10:26.000',0),
       (55,1,'2022-04-02 14:28:45.000',1),
       (56,1,'2022-04-02 14:32:19.000',0),
       (57,1,'2022-04-05 03:20:18.000',1),
       (58,3,'2022-04-05 03:21:18.000',1),
       (59,2,'2022-04-05 03:21:22.000',1),
       (60,2,'2022-04-06 02:27:15.000',0),
       (61,3,'2022-04-06 02:27:15.000',0),
       (62,1,'2022-04-06 02:28:17.000',0);

It works like when system become unavailable we get 1, when become available -> 0. I need to get a result table when can see how much hours each day each system was unavailable. For this table result should be

date          system                 available                             unavailable
2022-04-02      1      13:57:07+00:18:19+09:27:40 =23:43:06         23:59:59-23:43:06=..
2022-04-02      2                        24                                     0
2022-04-02      3                        24                                     0

2022-04-03      1                        24                                     0
2022-04-03      2                        24                                     0
2022-04-03      3                        24                                     0
...
2022-04-05      1                     03:20:18                     23:59:59-03:20:18=..
2022-04-05      3                     03:21:18                     23:59:59-03:21:18=..
2022-04-05      2                     03:21:22                     23:59:59-03:21:22=..

2022-04-06      1               23:59:59-02:28:17=..                       02:28:17
2022-04-06      3               23:59:59-02:27:15=..                       02:27:15
2022-04-06      2               23:59:59-02:27:15=..                       02:27:15

I try do it with over partition by and recursion, but get more interval, than I need.

Answer 1

I wrote a sample query for calculating intervals and showing intervals as hours using your table structure:

with d_test as (
    select 
        row_number() over (order by system_id, time) as r_num, 
        "time"::date as "onlydate",
        "time", 
        system_id, 
        status
    from temp_data_test
    order by system_id, time 
)
select avi.*, unavi."unavialible", unavi."unavialible_hours" from (
    select 
        d1.system_id, 
        d1.onlydate,
        sum(d2.time - d1.time) as "avialible",  
        (extract(day from sum(d2.time - d1.time) )*24 + extract(hour from sum(d2.time - d1.time)))::text || ' hours' as "avialible_hours"
    from d_test d1 
    inner join d_test d2 on d1.r_num+1 = d2.r_num and d1.system_id = d2.system_id 
    where d1.status = 1 
    group by d1.system_id, d1.onlydate
    order by d1.system_id 
) avi 
left join (
    select 
        d1.system_id, 
        d1.onlydate,
        sum(d2.time - d1.time) as "unavialible",  
        (extract(day from sum(d2.time - d1.time) )*24 + extract(hour from sum(d2.time - d1.time)))::text || ' hours' as "unavialible_hours"
    from d_test d1 
    inner join d_test d2 on d1.r_num+1 = d2.r_num and d1.system_id = d2.system_id 
    where d1.status = 0 
    group by d1.system_id, d1.onlydate
    order by d1.system_id
) unavi on avi.system_id = unavi.system_id and avi.onlydate = unavi.onlydate 

Result of this query:

system_id	onlydate	avialible	avialible_hours	unavialible	unavialible_hours
1	2022-04-02	00:16:53	0 hours	2 days 13:06:18	61 hours
1	2022-04-05	23:07:59	23 hours
2	2022-04-05	23:05:53	23 hours
3	2022-04-05	1 day 02:05:57	26 hours