Bigqueryで日付を操作する

皆様こんにちは

今回はGoogleBigqueryの日付のデータ型(DATE型)に関する
関数についてあれこれ掘下げて行きます。

1.SQLを実行した時点での日付を関数で呼び出す

SELECT
CURRENT_DATE() AS {任意のカラム名} –例としてUTC_DATEというカラム名にする

この場合呼び出しされる日付はUTCです。
なのでJSTにしたい場合は

SELECT
CURRENT_DATE(“Asia/Tokyo”) AS {任意のカラム名} –例としてJST_DATEというカラム名にする

※補足
UTCは世界協定時
JSTは日本標準時
UTCとJSTではJSTのほうが　9時間早い　ため、SQLを実行する時間によってはUTCのままだと
昨日の日付になってしまうことがありますので注意が必要です。

例:日本時間の2021年8月20日午前8時にSQLを実行した場合
2021-08-19　← UTCでは8月19日午後23時になる。

タイムゾーン早見表はこちらを参考にしています。
https://www.jisakeisan.com/?y=2021&m=8&d=16&hh=8&mm=0&t1=jst&t2=utc

2.SQLを実行した時点での日付の1日前(1ヵ月前、1年前も可)の
日付を関数で呼び出す

SELECT
DATE_SUB(CURRENT_DATE(“Asia/Tokyo”), INTERVAL 1 DAY) AS {任意のカラム名}

※補足
DAY　を　MONTHにすると　1ヵ月前
DAY　を　YEARにすると　1年前

◆応用
既に存在しているDATE型に対して1日追加する場合

SELECT
DATE_SUB({すでにある日付型のカラム},INTERVAL 1 DAY) AS {任意のカラム名}

3.SQLを実行した時点での日付の1日後(1ヵ月後、1年前も後)の
日付を関数で呼び出す

SELECT
DATE_ADD(CURRENT_DATE(“Asia/Tokyo”), INTERVAL 1 DAY) AS {任意のカラム名} 

※補足
DAY　を　MONTHにすると　1ヵ月後
DAY　を　YEARにすると　1年後

◆応用
既に存在しているDATE型に対して1日追加する場合

SELECT
DATE_ADD({すでにある日付型のカラム},INTERVAL 1 DAY) AS {任意のカラム名} 

4.うるう年の判定について

無効なうるう年を入れるとエラーが帰ってきます

■無効なうるう年の場合　SQLの実行不可能
SELECT DATE(2021,02,29) AS DATE
■有効なうるう年の場合　SQLの実行可能
SELECT DATE(2020,02,29) AS DATE

補足

DATE_SUBでうるう年の日付をINTERVAL 1 YEAR した場合どうなるか

SELECT
DATE_SUB(DATE(2020,02,29), INTERVAL 1 YEAR)
— 2019-02-28　が帰ってきてSQLの実行ができる

5.TIMESTAMP型のデータをDATE型に変換する

SELECT
FORMAT_DATE(“20%y-%m-%d”,CURRENT_TIMESTAMP()) AS NOW

もしくは

SELECT
CAST(CURRENT_TIMESTAMP() AS DATE) AS NOW

※補足
既に存在しているTIMESTAMP型のデータをDATE型に変換する場合

SELECT
FORMAT_DATE(“20%y-%m-%d”,{指定のTIMESTAMP型のカラム}) AS {任意のカラム名}
FROM T0

6.割と自由に日付等が広範囲に散らばってしまっている場合に結合する関数

日付はデータによっては12月がDec等で表記されているだけのものがあるのを
まとめて変換してくれる関数が存在しています。
但しこの関数は合致しない形式設定要素が変換する文字列内に日存在している場合はエラーでSQLを実行することができません。

形式設定要素とは

自動的に変換してくれる関数においては形式設定要素がどのような定義なのか確認しておくい必要があります。
今回は日付に焦点を当てているのでその他必要な形式設定要素については下記URLを参照願います。

%B　完全な月の名前 JanuaryやAugustといった三文字で省略されていない月名
%b　省略された月の名前で　JanuaryやAugustといった三文字で省略されていない月名で　例としてDec DEC どちらでも12月と認識される
%d　日付を表すところで　8日の場合は(08)と表記される
%e　日付を表すところで　8日の場合は(8)と表記されるが　yyyy-mm-ddの場合は08となる（ややっこしい)
%m　月を表すところで　8月の場合は(08)と表示される
%Y　年を表すところで　21の場合は0021年になってしまう
%y　年を表すところで　21の場合は2021年になる

色々弄ってみる用のSQLを作ってみたので是非お試しください

WITH T0 AS (
SELECT
“Aug” AS MONTH, –文字列型のデータ AUGにしてみてどうなるか　Augustにしてどうなるかお試しください
CAST(“8” AS INT64) AS DAY, –数字型のデータ 08のINT型だとどうなるか　STRING型だとどうなるかお試しください
“21” AS YEAR –文字列型のデータ
)
 
SELECT
PARSE_DATE(“%b%e%Y” ,CONCAT(MONTH,DAY,YEAR)), — 0021-08-08
PARSE_DATE(“%b%e%y” ,CONCAT(MONTH,DAY,YEAR)), — 2021-08-08
FROM T0

参照したドキュメント
コチラ

サインアップ

ログイン

パスワードをお忘れですか

1.SQLを実行した時点での日付を関数で呼び出す

2.SQLを実行した時点での日付の1日前(1ヵ月前、1年前も可)の日付を関数で呼び出す

3.SQLを実行した時点での日付の1日後(1ヵ月後、1年前も後)の日付を関数で呼び出す

4.うるう年の判定について

5.TIMESTAMP型のデータをDATE型に変換する

6.割と自由に日付等が広範囲に散らばってしまっている場合に結合する関数

関連記事

アップルストアでやっちゃいました！

海外留学・語学留学ならNext Education！

コメントを残すコメントをキャンセル

2.SQLを実行した時点での日付の1日前(1ヵ月前、1年前も可)の
日付を関数で呼び出す

3.SQLを実行した時点での日付の1日後(1ヵ月後、1年前も後)の
日付を関数で呼び出す

コメントを残す
コメントをキャンセル