google-bigquery - 给定两个时间戳，计算这些日期之间经过的工作小时数

Question

给定表中的两个时间戳 start_time, end_time计算它们之间发生的总工作时间。

工作时间在您选择的单独表格中定义。

Answer 1

假设您有一个工作时间表，这里是 Snowflake SQL 中的解决方案:

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

这是一篇文章，详细介绍了在 SQL 中和作为雪花中的 Javascript UDF 实现这一点的更多细节:
https://medium.com/dandy-engineering-blog/how-to-calculate-the-number-of-working-hours-between-two-timestamps-in-sql-b5696de66e51