SQL データサイエンス 入門

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

データ解析では、時刻の情報を扱う場面が多くあると思います。

また、Bigqueryを使用している人が増えていると思い、一度SQLのTIMESTAMP型に特化して、重要となってくる関数・文法についてまとめていこうと思います!!

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

JSTは日本標準時で、UTCとは9時間の時差があります。注意しましょう!!!

(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)

ある時刻から5時間後を取得するといった操作がしたいですよね

その時は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型など多くの時刻を扱うデータ型があります。

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

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


-SQL, データサイエンス, 入門
-, ,

© 2020 はやぶさエンジニア Powered by AFFINGER5