生成日期序列，其中包含每天'OFF'类型的小时数

Question

我正在为我的公司编写一个#PostgreSQL查询，用于计算一个人工作了多少小时，其中包含有关请求的信息，包含这些列:请求ID，发件人用户名，类型，开始时间，结束时间。

示例数据为:

tbody> <<tr>史密斯

请求ID	发送方用户名	类型	起始时间	结束时间
1		从	2023-04-01 8:00:00	2023-04-03 13:00:00

下面的代码可以满足您的要求:

with cte_ontimes as
(SELECT am_start as start_time, 
am_start + interval '210 minute' as end_time 
FROM generate_series
( '2023-03-28 08:30:00'::timestamp 
, '2023-04-10 08:30:00'::timestamp
, '1 day'::interval) am_start
UNION
SELECT pm_start as start_time, 
pm_start + interval '270 minute' as end_time 
FROM generate_series
( '2023-03-28 13:30:00'::timestamp 
, '2023-04-10 13:30:00'::timestamp
, '1 day'::interval) pm_start
ORDER BY 1),
cte as
(SELECT extract(hours 
from least(o.end_time, f.end_time) 
- greatest(o.start_time, f.start_time)) 
+ extract(minutes 
from least(o.end_time, f.end_time) 
- greatest(o.start_time, f.start_time)) / 60.0
as hoursoff, 
o.end_time::date as workday, f.sender_username 
FROM cte_ontimes o
INNER JOIN offtimes f ON f.start_time < o.end_time
and f.end_time > o.start_time AND f.type = 'OFF')
select sum(hoursoff) as daily_hours_off, workday, sender_username
FROM cte
GROUP BY workday, sender_username
ORDER BY workday, sender_username;

但是，我强烈建议您使用一个列出公司工作时间的表，而不是我在示例中使用的generate_series。为什么?周末是可以安排的，但是公共假日就很难安排了，更不用说特殊的半天了。

作为解释，我首先选择那些"关闭"的日期和时间。时间与公司上班时间一致。然后，我使用最大和最小函数来处理任何重叠—例如，如果您的样本下班时间从09:00而不是08:00开始，那么它将在工作时间内，因此我们希望从09:00而不是08:30开始计数。得到时间之后，提取小时和部分小时(分钟/60)就相对简单了。剩下的就是按日期和用户求和了。