如何计算javascript中两个时间戳之间的差异，不包括周末和晚上

Question

我目前正在努力找出确定两个时间戳之间的时间长度(以秒或更精确的单位(的最佳方法，同时排除周末和下班时间。

周末更容易处理，我只需要使用if语句就可以做到这一点，使用moment.js的ISO天/周，但事实证明，确定何时取消固定工作时间(8-5(之外的夜晚是相当困难的。即使最初或最终的时间戳恰好在休息时间或周末，我仍然希望计算所有的工作时间。

我将在一个可能有数百万行数据的UDF中在Google bigquery上运行这个，所以它也需要有一定的效率。非常感谢您的帮助。

Answer 1

如果您只对使用BQ持开放态度，请考虑BigQuery StandardSQL中的以下片段：

with data as (
select 1 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 1 day) as ts2 union all
select 2 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 2 day) as ts2 union all
select 3 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 3 day) as ts2 union all
select 4 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 4 day) as ts2 union all
select 5 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 5 day) as ts2 union all
select 6 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 6 day) as ts2 union all
select 7 as id, current_timestamp() as ts1, timestamp_add(current_timestamp(), interval 7 day) as ts2
),
temp as (
select * 
from data, unnest(generate_timestamp_array(timestamp_trunc(ts1,day),timestamp_trunc(ts2,day),interval 1 day)) calendar
)
select
id, ts1, ts2,
sum(
case 
-- Get # of Seconds on First Day from ts1 to 5:00PM
when timestamp_trunc(ts1,day) = timestamp_trunc(calendar,day)
then greatest(timestamp_diff(timestamp_add(calendar,interval 17 hour),ts1,second),0)
-- Get # of Seconds on Last Day from 8:00AM to ts2
when timestamp_trunc(ts2,day) = timestamp_trunc(calendar,day)
then greatest(timestamp_diff(ts2,timestamp_add(calendar, interval 8 hour),second),0)
-- Otherwise it is a full 8 hour day
else 8*60*60
end
) as working_seconds
from temp
where extract(dayofweek from calendar) not in (7,1) -- Exclude Saturday and Sunday
group by 1,2,3