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

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

Thanks in advance!

CodePudding user response：

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 

Fiddle

CodePudding user response：

Good answer from DannySlor... if you want something really easy to maintain and tweak (and scale), you can also wrap it in a function:

create or replace function fill_day(snapshot_date date)
returns table (xrows integer, xstart_date timestamp, xend_date timestamp, xtype text)
language plpgsql
as
$BODY$
  DECLARE
    rw record;
    last_date timestamp := snapshot_date;
  BEGIN
    xrows := 1;

    for rw in select *
              from table_a a
              where a.start_date::date = snapshot_date
              order by rows
    loop
      xstart_date := last_date;
      xend_date := rw.start_date - interval '1 minute';
      xtype := 'normal';
      return next;
      xrows := xrows   1;

      xstart_date := rw.start_date;
      xend_date := rw.end_date;
      xtype := 'failure';
      return next;
      xrows := xrows   1;

      last_date = rw.end_date   interval '1 minute';
    end loop;

    xstart_date := last_date;
    xend_date := snapshot_date   interval '1 day - 1 minute';
    xtype := 'normal';
    return next;
  END;
$BODY$

Here's how you would execute it:

select * from fill_day('2021-08-01')