sql - use generate series

Question

Im writing a psql procedure to read source table, then agregate and write in aggregate table. My table source contains 2 columns beg, and end refers to client connection to the website, and client disconnect. I want to caculate for each client the time that he spends . The purpose to use generate series is when the event is over one day.

My pseudo code is below

execute $$SELECT MAX(date_) FROM $$||aggregate_table INTO max_date;
IF max_date is not NULL THEN


execute $$DELETE FROM $$||aggregate_table||$$ WHERE date_ >= $$||quote_literal(max_date);
ELSE
  max_date := 'XXXXXXX';
end if;


SELECT * from (
   select
   Id, gs.due_date,
  (case
     When TRIM(set) ~ '^OPT[0-9]{3}/MINUTE/$'
     Then 'minute'
     When TRIM(set) ~ '^OPT[0-9]{3}/SECOND/$'
     Then 'second'
     as TIME, 
  sum(extract(epoch from (least(s.end, gs.date_ + interval '1 day') -
                           greatest(s.beg, gs.date_)
                          )
              ) / 60) as Timing
 from source s cross join lateral
generate_series(date_trunc(‘day’, s.beg), date_trunc('day',
     least(s.end,
     CASE WHEN $$||quote_literal(max_date)||$$ = ‘XXXXXXX’
          THEN (current_date)
          ELSE $$||quote_literal(max_date)||$$
     END)
  ), interval '1 day’) gs(date_)
  where ( (beg, end) overlaps ($$||quote_literal(max_date)||$$'00:00:00',    $$||quote_literal(max_date)||$$'23:59:59’))
group by id, gs.date_, TIME
 ) as X
where ($$||quote_literal(max_date)||$$ = X.date_  and $$||quote_literal(max_date)||$$ != ‘XXXXXXX’)
OR  ($$||quote_literal(max_date)||$$ ='XXXXXXX')

Data of table source

number, beg, end, id, set
(10, '2019-10-25 13:00:00', '2019-10-25 13:30:00', 1234, 'OPT111/MINUTE/'),
(11, '2019-10-25 13:00:00', '2019-10-25 14:00:00', 1234, 'OPT111/MINUTE/'),
(12, '2019-11-04 09:19:00', '2019-11-04 09:29:00', 1124, 'OPT111/SECOND/'),
(13, '2019-11-04 22:00:00', '2019-11-05 02:00:00', 1124, 'OPT111/MINUTE/')

Expected_output agregate table

2019-10-25, 1234, MINUTE, 90(1h30)
2019-11-04, 1124, SECOND, 10
2019-11-04, 1124, MINUTE, 120
2019-11-05, 1124, MINUTE, 120

The problem of my code is that, it diesn't work if i have new row that will be added tomorrow with for example (14, '2019-11-06 12:00:00', '2019-11-06 13:00:00', 1124, 'OPT111/MINUTE/').

Please guys who can help?

thank you

Answer 1

Here is my solution. I have changed column names in order to avoid reserved words. You may need to touch the formatting of duration.

with mycte as
(
 select -- the first / first and only days
    id, col_beg,
    case when col_beg::date = col_end::date then col_end else date_trunc('day', col_end) end as col_end
 from mytable 

 union all
 select -- the last days of multi-day periods
    id, date_trunc('day', col_end) as col_beg, col_end
 from mytable 
 where col_end::date > col_beg::date

 union all
 select -- the middle days of multi-day periods 
    id, rd as col_beg, rd::date + 1 as col_end
 from mytable
    cross join lateral generate_series(col_beg::date + 1, col_end::date - 1, interval '1 day') g(rd)
 where col_end::date > col_beg::date + 1
)
 select 
    col_beg::date as start_time, id, sum(col_end - col_beg) as duration
 from mycte group by 1, 2 order by 1;