如何计算在办公时间(9-5)和下班时间内的会议记录？

Question

我有一个datetime字段，它是一个活动开始的时间，以及一个带有active_time的int字段(分钟内)。

我想要做的是在几分钟内(然后是/60小时)计算出活动在工作时间(9-5小时)中花费的时间以及这些时间之外的时间。

例如，数据列

日期：'2022-02-28 16:54:00.000 +0000‘。

Active_time分钟：“20”

期望产出：

工作时间活动时间：“6”

下班时间：“14”

有人能帮忙吗？

非常感谢。

Answer 1

我想不出用内置日期函数来处理这个问题的方法，所以这也许不是最漂亮的解决方案，但数学是存在的。这将开始时间戳/活动时间转换为相对于您的工作时间的分钟。

我减去540，实质上将时间9:00设置为“分钟0”，以使数字更容易处理。这使得下午5:00“分钟480”

然后，这只是一个减去时间之内和外部的工作时间的问题。

set startdatetime = '2022-03-23 7:31:00'::timestamp_ntz;
set active_minutes = 125;
set business_start = 0;
set business_end = 480;

select 
-540 + (hour($startdatetime) * 60 + minute($startdatetime)) as start_minute,
start_minute + $active_minutes as end_minute,
-- End time (within business hours) - Start time (within business hours). Can't be less than 0
greatest(0, least(end_minute, $business_end) - greatest(start_minute, $business_start)) as minutes_during_business,
-- End - Start. With any "business minutes" ignored. first for pre-work minutes, then for post-work minutes
least(end_minute, $business_start) - least(start_minute, $business_start)
+ greatest(end_minute, $business_end) - greatest(start_minute, $business_end) as minutes_outside_business,
minutes_during_business / 60 as hours_during_business,
minutes_outside_business / 60 as hours_outside_business;
;

如果您的活动分钟跨越到第二天的营业时间，这将不能很好地工作。这需要额外的处理。

您还可以添加秒，并将所有硬编码的数字转换为秒(如果您确实需要额外的粒度)。