今回はGoogle BigQueryにおけるFORMAT_DATE関数の意味や使い方について紹介する。
FORMAT_DATE関数とは
FORMAT_DATE関数は日付を指定されたフォーマットの文字列として返してくれる関数だ。
例えば、sample_dataテーブルの下記date_fieldカラム（データ型はDATE型）を2023年10月5日という文字列に変更したい場合などにFORMAT_DATE関数は便利だ。
| date_field |
|---|
| 2020-07-04 |
| 2021-01-01 |
| 2022-05-15 |
| 2023-10-05 |
1 2 3 4 5 | SELECT date_field ,FORMAT_DATE('%Y年%m月%d日', date_field) as formatted_date FROM `sample_date`; |
上記クエリを実行すると下記の結果となる。
FORMAT_DATE関数により、2021-01-01という日付（DATE型）が2021年1月01日という文字列（STRING型）に変換されている。
このようにFORMAT_DATE関数は日付を簡単に指定した形式の文字列に変換してくれる便利な関数だ。
FORMAT_DATE関数の使い方
FORMAT_DATE関数の使い方は下記2つを指定すればOKだ。
- 指定フォーマット：対象カラムの変換形式の指定
- 対象カラム：変換対象の日付型カラム
注意したいのは、FORMAT_DATE関数で対象カラムに指定可能なのはDATE型のみということだ。
BigQueryにはDATE型以外にも下記のようにDATETIME型やTIMESTAMP型等がある。
しかし、FORMAT_DATE関数ではDATE型のみが対象となる。
そのため、DATE型以外の日時カラムを変換したい場合はFORMAT_DATETIME関数やFORMAT_TIMESTAMP関数を利用すると良いだろう。
また、FORMAT_DATE関数で変換後のカラムはデータ型がSTRING型（文字列）になる点も覚えておきたい。
では、実際どんなフォーマットに変換できるかを確認していこう。
FORMAT_DATE関数では下記のように日付カラムを様々なフォーマットに変換することが出来る。
下記の実際の表示例は2023年10月8日という値を指定フォーマットで変換するとどのように表示されるかを表している。
| フォーマット指定 | 実際の表示例 | 指定の意味 |
| %Y | 2023 | 年を4桁で表す |
| %y | 23 | 年を最後の2桁で表す |
| %m | 10 | 月を2桁の数字で表す |
| %b | Oct | 3文字の月の省略形で表す |
| %B | October | 月の完全な名前を表す |
| %d | 08 | 日を2桁の数字で表す |
| %e | 8 | 日を1桁または2桁で表す |
| %a | Sun | 3文字の曜日の省略形を表す |
| %A | Sunday | 曜日の完全な名前を表す |
例えば、2023/10/08という形式に変換したい場合のFORMAT_DATE関数の指定は下記の通りだ。
実際のクエリとその結果は下記のようになる。
1 2 3 4 5 | SELECT date_field ,FORMAT_DATE("%Y/%m/%d", date_field) as formatted_date FROM `sample_date`; |
もう1つ例を見ていこう。
date_fieldの各値の曜日をFORMAT_DATE関数で取得したクエリとその結果が下記だ。
1 2 3 4 5 | SELECT date_field ,FORMAT_DATE("%a", date_field) as formatted_date FROM `sample_date`; |
FORMAT_DATE関数はこのように使って日付を様々なフォーマットに変換することが出来る。
まとめ
FORMAT_DATE関数の要点としては下記だ。
- FORMAT_DATE関数は日付を様々な表示形式に変換してくれる関数
- 変換対象にできるのはDATE型のカラムのみ
- 変換後のデータはSTRING型（文字列)
- 活用シーンとしては曜日変換が結構便利
- WHERE句で曜日を指定して特定の曜日のデータを集計する等が出来る
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
