Query: generated start_date and end_date where it isn’t exist in the table

i want to ask something, so i have a table A that have start_date and end_date column to store the data when there is a failure happened, it look like this

rows	start_date	end_date
1	“2021-08-01 00:04:00”	“2021-08-01 02:54:00”
2	“2021-08-01 04:52:00”	“2021-08-01 05:32:00”

And what i want to do is to have a query so that the response will come out like this (for example i want to get the data of 1 August 2021), it fill a row between start_date and end_date that doesn’t exist in table and fill type as normal and otherwise fill it as failure

rows	start_date	end_date	type
1	“2021-08-01 00:00:00”	“2021-08-01 00:03:00”	normal
2	“2021-08-01 00:04:00”	“2021-08-01 02:54:00”	failure
3	“2021-08-01 02:55:00”	“2021-08-01 04:51:00”	normal
4	“2021-08-01 04:52:00”	“2021-08-01 05:32:00”	failure
5	“2021-08-01 05:53:00”	“2021-08-01 23:59:00”	normal

Thanks in advance!

Answer

We create dates between and unpivot them using union all. Then we add the last line and do the same.

with t2 as (
            select *,'failure' as type 
            from   t

  
union all 

  
            select  rows
                   ,coalesce(lag(end_date) over(order by end_date) + interval '1 minute', date_trunc('hour', start_date)) as start_date
                   ,start_date + interval '-1 minute' as end_date
                   ,'normal'
            from   t

  
union all

  
            select  max(rows) 
                   ,max(end_date) + interval '1 minute'
                   ,date_trunc('day', max(end_date)) + interval '1 day -1 minute'
                   ,'normal'
            from    t

order by rows, start_date
           )

select  row_number() over(order by start_date) as rows
       ,start_date
       ,end_date
       ,type
from    t2 

rows	start_date	end_date	type
1	2021-08-01 00:00:00	2021-08-01 00:03:00	normal
2	2021-08-01 00:04:00	2021-08-01 02:54:00	failure
3	2021-08-01 02:55:00	2021-08-01 04:51:00	normal
4	2021-08-01 04:52:00	2021-08-01 05:32:00	failure
5	2021-08-01 05:33:00	2021-08-01 23:59:00	normal

Fiddle