【Bigquery】SQLのTIMESTAMP型の関数をまとめ・一覧

【記事の信頼性】
日頃、Biqqueryを活用している現役エンジニアがTimestampに関する関数を解説していきます。

データ解析では、時刻の情報を扱う場面が多くあると思います。
また、Bigqueryを使用している人が増えていると思い、SQLのTIMESTAMP型に特化して、重要となってくる関数・文法についてまとめていこうと思います！！

よく困りごとで聞くので、先に書いておくと「JST」と「UTC」で基準とする時刻が異なるので注意です。

JSTは日本標準時で、UTCとは９時間の時差があります。注意しましょう！！！
（UTCは協定世界時であり、世界基準で定めた時刻です）

SQLのTIMESTAMP型の関数まとめ・一覧

CURRENT_TIMESTAMP(現在時刻の取得）

誰もがまずやるはずの現在時刻の取得です！！
TIMESTAMP形式で取得するためには「CURRENT_TIMESTAMP」を使います

CURRENT_TIMESTAMP()
> 2020-08-26 10:00:00

上記のようなSQLで現在時刻を取得できます。

一つ注意したいのは、JSTとUTCの違いです。
UTCで取得されることに注意してくださいね！

TIMESTAMP_ADD(時刻の足し算)

時刻の足し算です。TIMESTAMP_ADDで足し算が可能です！

具体例と共に見ていきましょう！！
時刻の単位（SECOND,MINUTE,HOUR,DAY,MONTH,YEAR)を指定してあげると色々な足し算ができます！！

TIMESTAMP_ADD(時刻, INTERVAL ? SECOND
                              MINUTE
                              HOUR
                              DAY
                              MONTH
                              YEAR)

ある時刻から５時間後を取得するといった操作がしたいですよね
その時はINTERVALと書いた後に5 HOURと書いてあげるだけです！！

TIMESTAMP_ADD("2020-08-26 10:00:00", INTERVAL 5 HOUR)
> "2020-08-26 15:00:00"

現在時刻から20分足したい時は下のようにします

TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 20 MINUTE)

TIMESTAMP_SUB(時刻の引き算)

さて、時刻の引き算についてです。

TIMESTAMP_SUBを使うと引き算が可能です。

TIMESTAMP_SUB(時刻, INTERVAL ? SECOND
                              MINUTE
                              HOUR
                              DAY
                              MONTH
                              YEAR)

ある時刻から3日前を取得するといった操作ができます

TIMESTAMP_SUB("2020-08-26 19:00:00", INTERVAL 3 DAY)
> "2020-08-23 19:00:00"

現在時刻から20秒前は下のようにします

TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 SECOND)

TIMESTAMP_TRUNC(時刻の切り捨て)

時刻の切り捨てには TIMESTAMP_TRUNCを使います。

例えば、12:25:30を12:00:00に綺麗な時刻に整形したいというような、秒を切り捨てたい、分以下を切り捨てたいという場面で活躍します！

TIMESTAMP_TRUNC(時刻, MINUTE
                     HOUR
                     DAY
                     MONTH  # MONTHで切り捨てると日が01になります
                     YEAR   # YEARで切り捨てると月日が01-01になります
)

下の例は分以下を切り捨てる場合のコードです。

TIMESTAMP_TRUNC("2020-08-26 10:32:14", YEAR)
> "2020-01-01 00:00:00"

現在時刻の秒だけ切り捨てたい時は下のようにします

TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, MINUTE)

まとめ

いかがだったでしょうか？？

時刻、時間のデータを扱う場面がデータ解析、分析ではたくさんあります。

今回はTIMESTAMP型に限定して紹介しましたが、DATE型、TIME型など多くの時刻を扱うデータ型があります。

今後、他のデータ型についても紹介していきます！！

ありがとうございました。