サインアップ

ログイン

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

パスワードを忘れましたか? メールアドレスを入力してください。 リンクを受け取り、メールで新しいパスワードを作成します。

質問するにはログインする必要があります

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
 
 
参照したドキュメント
コチラ

関連記事

コメントを残す