[bigquery]時間差分を取得する方法

2022-05-08 2022-06-05

bigqqueryで日時情報が入った２つの項目の差分から、時間差分(xx:xx:xx)を表示する時のサンプルコード

SELECT
  CONCAT
  (
    CAST(DATETIME_DIFF(CAST(end_at AS DATETIME), CAST(start_at AS DATETIME), hour) AS string) , ":",
    CAST(mod(DATETIME_DIFF(CAST(end_at AS DATETIME), CAST(start_at AS DATETIME), minute),  60) AS string),  ":", 
    CAST(mod(DATETIME_DIFF(CAST(end_at AS DATETIME), CAST(start_at AS DATETIME), second),  60) AS string) 
  ) AS diff_time