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:
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.