PostgreSQL考勤和夜班

Question

我有下表：

dt	类型
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

通过将每个工作日(开始、休息开始、休息结束、结束(分组为一个工作日，我们可以使用crosstab来调整它，将每组的第一个工作日作为全天的工作日。

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)

中断结束工作结束02:30:0006:00:0010:27:00>13:00:00

dt	工作启动	中断启动
2022-09-12	21:36:26	02:00:00
2022-09-20	06:00:00	10:00:00