BigQueryで秒数を時分秒に変換する

この記事はなに？

例えば、3752という秒数の整数値を持つカラムを、1時間2分32秒に変換するSQLの書き方を紹介した記事になります。

秒数に限らず応用できるので、ぜひご活用ください。

結論

秒数を表すsecondsというカラムに、3752という値が入っているとします。

SELECT 
  seconds
FROM
  `project.dataset.table`

seconds
3752

これを1時間2分32秒に変換するにはMAKE_INTERVAL関数とEXTRACT関数を使います。

SELECT 
  seconds,
  CONCAT(
    EXTRACT(HOUR FROM MAKE_INTERVAL(SECOND => seconds)), '時間', 
    EXTRACT(MINUTE FROM MAKE_INTERVAL(SECOND => seconds)), '分', 
    EXTRACT(SECOND FROM MAKE_INTERVAL(SECOND => seconds)), '秒'
  ) AS format_seconds
FROM 
  `project.dataset.table`

seconds	format_seconds
3752	1時間2分32秒

解説

MAKE_INTERVAL関数は、年、月、日、時、分、秒を表す整数値を渡すと、INTERVALオブジェクトを返してくれます。
引数はすべて省略可能なので、今回は秒を表す整数値のみを渡すことにしました。

EXTRACT関数は、INTERVALオブジェクトから、指定した日時形式の値のみを抽出してくれます。
今回は、1時間2分32の形式で出力したかったため、3つのEXTRACT関数の結果をCONCATで結合させています。

おわりに

今回の記事を書くにあたり、INTERVAL関数の存在を初めて知りました。
60や3600で割って、愚直に計算するよりもスマートになっていいですね。
この記事が1人でも多くの人の役に立てば幸いです。
ここまで読んでくださりありがとうございました！