Question 114460 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search