十进制价值的日期差异，不包括周末

Question

我想在两个时间戳（例如1.45天）之间获得确切的天数。事情是BigQuery日期函数，这一天只接受2个时间戳论点。

SELECT
datediff(start_time_pac_tz, end_time_pac_tz) as Date_difference

date_difference

-6

我也希望排除周末。任何帮助都非常感谢。

Answer 1

使用 TIMESTAMP_TO_USEC(<timestamp>)，然后花一天 * 60 * 60 * 24。

SELECT
    ((TIMESTAMP_TO_USEC(end_time_pac_tz) - TIMESTAMP_TO_USEC(start_time_pac_tz)) / (60 * 60 * 24) + 1)
  -((WEEK(end_time_pac_tz) - WEEK(start_time_pac_tz)) * 2)
  -(CASE WHEN DAYOFWEEK(start_time_pac_tz) = 1 THEN 1 ELSE 0 END)
  -(CASE WHEN DAYOFWEEK(end_time_pac_tz) = 7 THEN 1 ELSE 0 END)
  as Date_difference