PostgreSQL Attendance and night shift

Question

i have following table:

dt	type
2022-09-12 21:36:26	WORK_START
2022-09-13 02:00:00	BREAK_START
2022-09-20 06:00:00	WORK_START
2022-09-20 10:00:00	BREAK_START
2022-09-20 10:27:00	BREAK_END
2022-09-20 13:00:00	WORK_END
2022-09-13 06:00:00	WORK_END
2022-09-13 02:30:00	BREAK_END

and query :

SELECT g.tempDatum::date as datum,
   MAX(att.dt::time) FILTER (WHERE att.type = 'WORK_START') as work_start
,  MAX(att.dt::time) FILTER (WHERE att.type = 'BREAK_START') as break_start
,  MAX(att.dt::time) FILTER (WHERE att.type = 'BREAK_END') as break_end
,  MAX(att.dt::time) FILTER (WHERE att.type = 'WORK_END') as work_end
FROM generate_series( '2022-09-01','2022-09-30', '1 day'::interval) AS g(tempDatum)
LEFT JOIN att ON att.dt::date = g.tempDatum::date group by g.tempDatum order by 
g.tempDatum;

Result is pretty good:

Result photo

except for 2022-09-12 because is a night shift. I want move Break_start + end and work_end to day 2022-09-12 for better result as attendance log.

How achieve this ? Big thanks for any help.

Answer 1

By grouping each work day (start, break start, break end, end) as one we can use crosstab to pivot it using the first work day of each group as the one for the entire day as requested.

select *
from   crosstab(
       'select min(dte) over(partition by grp), type, tme from 
        (
        select dt::date as dte
              ,dt::time as tme
              ,type
              ,row_number() over(order by dt,type)-case when row_number() over(order by dt,type) <= 4 then row_number() over(order by dt,type) else row_number() over(order by dt,type)-4 end as grp
        from   t
        ) t'   )
as     ct(dt date, WORK_START time, BREAK_START time, BREAK_END time, WORK_END time)

dt	work_start	break_start	break_end	work_end
2022-09-12	21:36:26	02:00:00	02:30:00	06:00:00
2022-09-20	06:00:00	10:00:00	10:27:00	13:00:00

Fiddle