【SQL】日付コードまとめ（今日・日付差・文字変換）【BigQuery・Redshift】

SQLではよく日付を扱いますが、つい記法を忘れてしまいますよね。

ということで、よく使う日付コードを思いつく限りでまとめてみました。

アセロラ

BigQueryとRedshiftでSELECTする場面に特化してますが、それ以外の環境でも多少参考になるかもです。
こういうSQLを書きたいというリクエストがあったら記事にするのでコメントください！

日付型と日時型

このページではDATE型を日付型、DATETIME型（BigQuery）とTIMESTAMP型（Redshift）を日時型として呼んでいます。

CURRENT_DATE:「今日」を取得

-- BigQuery
CURRENT_DATE()

--- Redshift
CURRENT_DATE

アセロラ

BigQueryの場合は引数にタイムゾーンを指定できるので、日本時間なら「’Asia/Tokyo’」としましょう。
デフォルトがUTC（日本時間-9時間）なので、朝9時以前に使うと前日になります。

え、業務は9時以降だから関係ない？一理あるな。。（ない）

CURRENT_DATETIME:「現在時刻」を取得

-- BigQuery
CURRENT_DATETIME()

--- Redshift
CURRENT_TIMESTAMP
GET_DATE()

アセロラ

Redshiftは2通りの方法があるみたいでした。
TRUNC()関数と組み合わせてTRUNC(GET_DATE())のようにすると、CURRENT_DATEと同様「今日」を取得できるらしいですよ。

EXTRACT, DATE_PART: 年・月・日・週などを取得

--- dateを日付または日時型のカラムだとして

-- BigQuery
EXTRACT(YEAR FROM date),  -- 年数値
EXTRACT(MONTH FROM date), -- 月数値
EXTRACT(DAY FROM date), -- 日数値
EXTRACT(WEEK FROM date), -- 日〜土が0〜6の曜日番号
EXTRACT(WEEK(MONDAY) FROM date) -- 月〜日が0〜6の曜日番号

--- Redshift
DATE_PART(YEAR, date),  -- 年数値
DATE_PART(MONTH, date), -- 月数値
DATE_PART(DAY, date), -- 日数値
DATE_PART(DAYOFWEEK, date) -- 月〜日が1〜7の曜日番号

アセロラ

週を取り出したいときに仕様が違って厄介ですね。

DATE_SUB, DATEADD: 「昨日」や「明日」を計算

-- BigQuery
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

--- Redshift
DATEADD(DAY, -1, CURRENT_DATE)

アセロラ

BigQueryの場合はDATE_SUBで今日から1日引きます。
RedshiftにはDATEADDしかないようなので、マイナス1日を足すことで表現します。

引数の個数や順序も微妙に違うので注意。

DATE_TRUNC: 「月初日」を計算

-- BigQuery
DATE_TRUNC(CURRENT_DATE(), MONTH)  -- 今月の1日を表す

--- Redshift
DATE_TRUNC('MONTH', CURRENT_DATE)  -- 今月の1日を表す

アセロラ

DATE_TRUNCは日付・日時型の切り捨てを意味する関数。
これを利用して月情報より細かいものを切り捨てると、2022-08-16だったものが2022-08-01のように日付が1日に揃えられる。

BigQueryとRedshiftでは、引数順や日時単位の記法に違いがあるので注意。

LAST_DAY: 「月末日」を計算

-- BigQuery
LAST_DAY(CURRENT_DATE())  -- 今月の月末日を表す
LAST_DAY(CURRENT_DATE(), YEAR)  -- 今年の年末日を表す
LAST_DAY(CURRENT_DATE(), WEEK(MONDAY))  --今週の週末日（日曜日）を表す

--- Redshift
LAST_DAY(CURRENT_DATE)  -- 今月の月末日を表す

アセロラ

月によって異なるやっかいな月末日のために、LAST_DAY関数が用意されています。
BigQueryの場合は月末日だけでなく年や週を基準に末日を計算できます。

DATE_TRUNC: 「週の初日」を計算

-- BigQuery
DATE_TRUNC(CURRENT_DATE(), WEEK)  -- 今週の日曜日になる
DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))  -- 今週の月曜日になる

--- Redshift
DATE_TRUNC('WEEK', CURRENT_DATE)  -- 今週の月曜日になる

アセロラ

DATE_TRUNCは週基準で切り捨てることもできるので、週単位でGROUP BYしたいときに便利です。
BigQueryは何曜日を基準に週を数えるか指定できるので融通が効きますね。

DATE_DIFF: 日付差・日時差を取得

-- BigQuery
DATE_DIFF(date_new, date_old, DAY)  -- 2022-01-01から今日までの間に存在する日数

--- Redshift
DATEDIFF(DAY, date_old, date_new)  -- 2022-01-01から今日までの間に存在する日数

アセロラ

BigQueryは（新しい日付, 古い日付, DAY）という順なのに対し、Redshiftは（DAY, 古い日付, 新しい日付）という順が正解。
逆にすると負の値になるので気をつけましょう。

関数名にアンダースコア有無の違いがある点にも注意。

PARSE_DATE, TO_DATE: 文字列を日付・日時型に変換

-- BigQuery
DATE('2022-08-01'),  -- 日付
DATETIME('2022-08-01 17:00:00'),  -- 日時
PARSE_DATE('%Y年%m月%d日', '2022年8月1日')  -- 文字列をパースして日付型にする

--- Redshift
DATE('2022-08-01') -- 日付
TO_TIMESTAMP('2022-08-01 17:00:00', 'YYYY-MM-DD HH24:MI:SS'), -- 日時
TO_DATE('2022年8月1日', 'YYYY年MM月DD日'), -- 文字列をパースして日付型にする

アセロラ

BigQueryの場合はDATEやDATETIMEといった関数を使うことで、ハイフンやスラッシュ区切りされた文字列を直感的に変換できます。
特殊なフォーマットになっている場合はPARSE_DATEで指定してあげることになります。

RedshiftはDATEを利用できます。
フォーマット書式の指定にはTO_DATEやTO_TIMESTAMPを使います。
書式指定の方法がBigQueryとはちょっと違うので注意しましょう。

書式指定の方法については、この記事にある「おまけ：日付フォーマット書式子（YYYY-mm-dd）」も合わせて参考にしてください。

FORMAT_DATE, TO_CHAR: 日付・日時型を文字列に変換

-- BigQuery
FORMAT_DATE('%Y年%m月%d日', CURRENT_DATE()) --年月日表記の文字列にする

--- Redshift
TO_CHAR(CURRENT_DATE, 'YYYY年MM月DD日') -- 年月日表記の文字列にする

アセロラ

BigQueryではFORMAT_DATEを利用します。
RedshiftではTO_CHARを利用します。

こちらも引数順やフォーマット書式の指定の違いに注意。

おまけ：日付・日時リストを作成

-- BigQuery
SELECT
  * 
FROM
  UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-08-16')) AS date

--- Redshift
SELECT
  '2022-01-01'::DATE + (ROW_NUMBER() OVER () -1 || 'days')::INTERVAL AS date
FROM
  large_table
LIMIT 228

アセロラ

BigQueryはGENERATE_DATE_ARRAYで日付の配列を作り、テーブルとして展開できるようにUNNESTを適用しています。
AS dateをつけると列名をがdateになります。

一方Redshiftの場合はGENERATE_DATE_ARRAYどころか配列生成の関数がないので自力で頑張る必要があります。
十分な行数のある適当なテーブルの行番号をROW_NUMBER() OVER ()で取得し、行番号分の日数を基準の日付からずらすことで、擬似的に日付リストを作成しています。
最後に必要な行数をLIMITで制御するか、WHEREで絞るかなどして完成です。面倒臭すぎる。

おまけ：日付フォーマット書式子（YYYY-mm-dd）

こちらもBigQueryとRedshiftで微妙に仕様が異なるので、よく使うものを比較表にしてまとめておこうと思います。

意味	サンプル	BigQueryの場合	Redshiftの場合
4桁の年	2022	%Y	YYYY
2桁の月	08	%m	MM
2桁の日	16	%d	DD
年月日	2022-08-16	%F	YYYY-MM-DD
曜日（番号）	1	%w（0-6：日-月）	D（1-7：日-月）
曜日（文字）	Wed	%a	-（なし）
2桁の時（24時間）	19	%H	HH24
2桁の分	20	%M	MI
2桁の秒	15	%S	SS
時分秒	19:20:15	%T	HH24:MI:SS