BigQueryでTIMESTAMP型の差をhh:mm:ssで出力したい

Question

次のような表の時刻の差をhh:mm:ssで出力しようと考えていますがいい方法が思いつきません

+-----------------------------+-------------------------+
| start                       | finish                  |
+-----------------------------+-------------------------+
| 2018-11-19 17:02:29:656 UTC | 2018-11-21 19:32:40 UTC |
+-----------------------------+-------------------------+

目的の出力は以下のようになります。

+-----------------------------+-------------------------+-----------+
| start                       | finish                  | time_diff |
+-----------------------------+-------------------------+-----------+
| 2018-11-19 17:02:29:656 UTC | 2018-11-21 19:32:40 UTC | 50:30:11  |
+-----------------------------+-------------------------+-----------+

今考えているやり方としては、2つのTIMESTAMPの差をSECONDで出力し、それをhh:mm:ssの形に変換しようと思っているのですが使えそうな関数が見つかりません。

select
  start, finish,
  TIMESTAMP_DIFF(finish, start, SECOND) as totalTime
from table

よい方法があればご教授いただけると助かります。

Answer 1

結果はSTRING型になりますが、時,分,秒別に差分を計算して
CONCATで結合すれば、以下のような形で出力は可能かと思いますがいかがでしょうか？

クエリ

SELECT
  DATETIME "2018-11-19 17:02:29" as start,
  DATETIME "2018-11-21 19:32:40" as finish,
  CONCAT(
    cast(DATETIME_DIFF(DATETIME "2018-11-21 19:32:40", DATETIME "2018-11-19 17:02:29", hour) as string) , ":",
    cast(mod(DATETIME_DIFF(DATETIME "2018-11-21 19:32:40", DATETIME "2018-11-19 17:02:29", minute),  60) as string),  ":", 
    cast(mod(DATETIME_DIFF(DATETIME "2018-11-21 19:32:40", DATETIME "2018-11-19 17:02:29", second),  60) as string) 
  ) as time_diff;

結果

行	start	finish	diff	
1	2018-11-19T17:02:29	2018-11-21T19:32:40	50:30:11

Answer 2

TIMESTAMP_SECONDSとFORMAT_TIMESTAMPを組み合わせればよさそうに思います。

select
  start, finish,
  FORMAT_TIMESTAMP('%T',TIMESTAMP_SECONDS(TIMESTAMP_DIFF(finish, start, SECOND))) as totalTime
from table

Comments

[robert]

回答ありがとうございます。
この方法で試させていただきましたが、HH:MM:SSだけ抜き出していて日付が変わる場合（＝間隔が24時間以上の場合）に望む結果が得られませんでした。

[sazi]

hh:mm:ssの書式なら24時間以内が前提です。
２４時間を超えて時・分・秒の形式ならそれぞれ割り算で求めるだけですね