Bigqueryのtimestamp型など時刻・日付データのクエリを徹底解説

★本記事は10分で読み終わることができます。

現役エンジニアである筆者Bigqueryで利用できる時刻・日付データのクエリを解説しています。

データを分析するときに、時刻データは必須ですね。

Bigqueryで注意して欲しいのは、JSTとUTCの取り扱いです。

前提として、JSTは日本標準時で、UTCは世界標準時です。JSTの方がUTCに比べて９時間の早いので注意しましょう。

そして、JSTのデータでもBigqueryではUTCと表記されてしまうことがあります。

カラム名に「time_jst」と工夫することをオススメします。

Bigqueryについての詳しい説明は以下の記事を参考にしてください。

GoogleのBigqueryとは？現役エンジニアが丁寧に解説

【BigQuery で学ぶ非エンジニアのための SQL データ分析入門】

UdemyでBigqueryのためのSQLを学ぶ講座があります。

操作方法から文法まで体系的に学ぶことができるので、1500円でセール中に購入して受講してみてくださいね。

受講はこちら

現在時刻・日付を取得するクエリ
- CURRENT_TIMESTAMP()、CURRENT_DATE()、CURRENT_TIME()
日付・時刻の計算
様々な値を抽出する
- EXTRACT(取り出したい部分 FROM 時刻);
  - 年、月、日、時、分、秒
  - 曜日(DAYOFWEEK)
- 何日目（DAYOFYEAR）
まとめ

現在時刻・日付を取得するクエリ

まずは基礎である、現在時刻の取得ですね。

CURRENT_TIMESTAMP()、CURRENT_DATE()、CURRENT_TIME()

取得するデータ型にはTIMESTAMP、DATE、TIMEがあります。

SELECT CURRENT_TIMESTAMP();

#結果　2021-03-05 13:52:26 UTC

SELECT CURRENT_DATE();

#結果 2021-03-05

SELECT CURRENT_TIME();

#結果　13:52:26

日付・時刻の計算

時刻データの加算・減算・切り捨てなどを解説していきます。

加算：TIMESTAMP_ADD()、DATE_ADD()、TIME_ADD()

時刻の加算を具体例と共に見ていきましょう。

時刻の単位（SECOND, MINUTE, HOUR, DAY, MONTH, YEAR)を指定してあげると色々な足し算ができます。

もちろん、DATE型は年月日、TIME型は時分秒の加算しかできません。

#TIMESTAMPの計算
TIMESTAMP_ADD(時刻, INTERVAL ? SECOND)
TIMESTAMP_ADD(時刻, INTERVAL ? MINUTE)
TIMESTAMP_ADD(時刻, INTERVAL ? HOUR)
TIMESTAMP_ADD(時刻, INTERVAL ? DAY)
TIMESTAMP_ADD(時刻, INTERVAL ? MONTH)
TIMESTAMP_ADD(時刻, INTERVAL ? YEAR)

#DATEの計算
DATE_ADD(時刻, INTERVAL ? DAY)
DATE_ADD(時刻, INTERVAL ? MONTH)
DATE_ADD(時刻, INTERVAL ? YEAR)

#TIMEの計算
TIME_ADD(時刻, INTERVAL ? SECOND)
TIME_ADD(時刻, INTERVAL ? MINUTE)
TIME_ADD(時刻, INTERVAL ? HOUR)

ある時刻から5時間後を取得するといった処理は以下のように書きます。

その時はINTERVALと書いた後に5 HOURと書いてあげるだけです。

TIMESTAMP_ADD("2021-03-05 10:00:00", INTERVAL 5 HOUR)

#結果 "2021-03-05 15:00:00"

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

TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 20 MINUTE)

減算：TIMESTAMP_SUB()、DATE_SUB()、TIME_SUB()

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

〇〇_SUBを使うと減算が可能です。

#TIMESTAMPの計算
TIMESTAMP_SUB(時刻, INTERVAL ? SECOND)
TIMESTAMP_SUB(時刻, INTERVAL ? MINUTE)
TIMESTAMP_SUB(時刻, INTERVAL ? HOUR)
TIMESTAMP_SUB(時刻, INTERVAL ? DAY)
TIMESTAMP_SUB(時刻, INTERVAL ? MONTH)
TIMESTAMP_SUB(時刻, INTERVAL ? YEAR)

#DATEの計算
DATE_SUB(時刻, INTERVAL ? DAY)
DATE_SUB(時刻, INTERVAL ? MONTH)
DATE_SUB(時刻, INTERVAL ? YEAR)

#TIMEの計算
TIME_SUB(時刻, INTERVAL ? SECOND)
TIME_SUB(時刻, INTERVAL ? MINUTE)
TIME_SUB(時刻, INTERVAL ? HOUR)

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

TIMESTAMP_SUB("2021-03-05 19:00:00", INTERVAL 3 DAY)

#結果 "2021-03-02 19:00:00"

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

TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 SECOND)

切り捨て：TIMESTAMP_TRUNC()、DATE_TRUNC()、TIME_TRUNC()

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

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

切り捨てられた部分は基本的に00もしくは01が入ります。

#TIMESTAMPの計算
TIMESTAMP_TRUNC(時刻, SECOND)
TIMESTAMP_TRUNC(時刻, MINUTE)
TIMESTAMP_TRUNC(時刻, HOUR)
TIMESTAMP_TRUNC(時刻, DAY) #切り捨てで01が入る
TIMESTAMP_TRUNC(時刻, MONTH) #切り捨てで01が入る
TIMESTAMP_TRUNC(時刻, YEAR) #切り捨てで01が入る

#DATEの計算
DATE_TRUNC(時刻, DAY) #切り捨てで01が入る
DATE_TRUNC(時刻, MONTH) #切り捨てで01が入る
DATE_TRUNC(時刻, YEAR) #切り捨てで01が入る

#TIMEの計算
TIME_TRUNC(時刻, SECOND)
TIME_TRUNC(時刻, MINUTE)
TIME_TRUNC(時刻, HOUR)

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

TIMESTAMP_TRUNC("2021-03-05 10:32:14", YEAR)
> "2021-03-05 00:00:00"

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

TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, MINUTE)

差分：TIMESTAMP_DIFF()、DATE_DIFF()、TIME_DIFF()

〇〇_DIFFで二つの時刻の差分が整数で取得できます。

どの単位で差分を取りたいかを指定してあげましょう。

#TIMESTAMPの計算
TIMESTAMP_DIFF(時刻1, 時刻2, SECOND)
TIMESTAMP_DIFF(時刻1, 時刻2, MINUTE)
TIMESTAMP_DIFF(時刻1, 時刻2, HOUR)
TIMESTAMP_DIFF(時刻1, 時刻2, DAY) 
TIMESTAMP_DIFF(時刻1, 時刻2, MONTH)
TIMESTAMP_DIFF(時刻1, 時刻2, YEAR)

#DATEの計算
DATE_DIFF(時刻1, 時刻2, DAY)
DATE_DIFF(時刻1, 時刻2, MONTH)
DATE_DIFF(時刻1, 時刻2, YEAR) 

#TIMEの計算
TIME_DIFF(時刻1, 時刻2, SECOND)
TIME_DIFF(時刻1, 時刻2, MINUTE)
TIME_DIFF(時刻1, 時刻2, HOUR)

TIMESTAMPでの例です。

もちろんマイナスの計算結果も取得できます。

SELECT TIMESTAMP_DIFF("2021-03-05 19:21:56", "2021-03-02 10:32:14", DAY);
#結果　3 (DAY)

SELECT TIMESTAMP_DIFF("2021-03-02 10:32:14", "2021-03-05 19:21:56", DAY);
#結果　-3 (DAY)

SELECT TIMESTAMP_DIFF("2021-03-05 19:21:56", "2021-03-02 10:32:14", HOUR);
#結果　80 (HOUR)

ビッグデータ分析・活用のためのSQLレシピ

created by Rinker

¥4,180 (2024/04/27 22:50:10時点 Amazon調べ-詳細)

様々な値を抽出する

EXTRACT(取り出したい部分 FROM 時刻);

年、月、日、時、分、秒

具体例で見ていった方が早いので以下を見てください。

#TIMESTMAPの場合
EXTRACT(SECOND FROM 時刻);
EXTRACT(MINUTE FROM 時刻);
EXTRACT(HOUR FROM 時刻);
EXTRACT(DAY FROM 時刻);
EXTRACT(MONTH FROM 時刻);
EXTRACT(YEAR FROM 時刻);

#TIMEの場合
EXTRACT(DAY FROM 時刻);
EXTRACT(MONTH FROM 時刻);
EXTRACT(YEAR FROM 時刻);

#TIMEの場合
EXTRACT(SECOND FROM 時刻);
EXTRACT(MINUTE FROM 時刻);
EXTRACT(HOUR FROM 時刻);

#TIMESTMAPの場合
EXTRACT(SECOND FROM TIMESTAMP("2021-03-05 19:21:56"));
# 56

EXTRACT(MINUTE FROM TIMESTAMP("2021-03-05 19:21:56"));
# 21

EXTRACT(HOUR FROM TIMESTAMP("2021-03-05 19:21:56"));
# 19

EXTRACT(DAY FROM TIMESTAMP("2021-03-05 19:21:56"));
# 5

EXTRACT(MONTH FROM TIMESTAMP("2021-03-05 19:21:56"));
# 3

EXTRACT(YEAR FROM TIMESTAMP("2021-03-05 19:21:56"));
# 2021

曜日(DAYOFWEEK)

TIMESTAMPやDATEを与えると、それが「週の中での何日目か」が取得できます。
結果は 1(日曜日)から7(土曜日)の整数となります。

#TIMESTMAPの場合
EXTRACT(DAYOFWEEK FROM TIMESTAMP("2021-03-05 19:21:56"));

# 6

何日目（DAYOFYEAR）

TIMESTAMPやDATEを与えると、それが「年の何日目か」を 1 から 366 の整数で返します。
たとえば「1」は「1月1日」を表します。

366は「うるう年」に対応しているからですね。

#TIMESTMAPの場合
EXTRACT(DAYOFYEAR FROM TIMESTAMP("2021-03-05 19:21:56"));

# 64

まとめ

今回は、Bigqueryにおける時刻・日付を扱うクエリを紹介しました。

データ分析において、時刻データは必ず処理することになるので、しっかりと理解しておきましょう！