Snowflake两次约会之间的工作时间

Question

如何在不创建表的情况下计算雪花中两个日期之间的工作时间？我尝试过(datediff(和时间戳之类的功能，但我无法找到解决方案

我想买那种

+---------------------+---------------------+---------------+
|       create_Task   |        Solved_Task  | BusinessHours |
+---------------------+---------------------+---------------+
| 2012-03-05 09:00:00 | 2012-03-05 15:00:00 |  6.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 10:00:00 |  8.000000     |
| 2012-03-05 11:00:00 | 2012-03-06 10:00:00 |  7.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 15:00:00 | 13.000000     |
| 2012-03-09 16:00:00 | 2012-03-12 10:00:00 |  2.000000     |
| 2012-03-06 16:00:00 | 2012-03-15 10:00:00 | 50.000000     |
| 2012-03-09 16:00:00 | 2012-03-19 10:00:00 | 42.000000     |
+---------------------+---------------------+---------------+

我想指定工作时间，这样我就可以计算的营业时间

Answer 1

实现这一点的一种方法是创建一个工作时间表。然后您可以运行一个相当简单的查询：

select
t.id
, sum(datediff(‘second’, 
-- calculate the max of the two start time
(case when t.start <= 
w.working_day_start_timestamp
then w.working_day_start_timestamp
else t.start
end),
-- calculate the min of the two end times
(case when t.end >= 
w.working_day_end_timestamp
then w.working_day_end_timestamp
else t.end 
end)
)) / 3600 -- convert to hourly
as working_hour_diff
from 
working_days_times w,
cross join time_intervals t
where -- select all intersecting intervals
(
t.start <= w.working_day_end_timestamp
and
t.end >= w.working_day_start_timestamp
)
and -- select only working days
w.is_working_day
group by
t.id

如果您需要一个函数，本文将描述Snowflake中Javascript UDF的实现：https://medium.com/dandy-engineering-blog/how-to-calculate-the-number-of-working-hours-between-two-timestamps-in-sql-b5696de66e51