この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の17日目の記事となります。
さがらです。
Snowflakeで扱うデータの中でも特に目にすることの多い、日付や時刻のデータを扱う関数について、試してみたので本記事でまとめてみます。※この記事では「加算/減算・切り捨て」に関わる関数について書きます。
日付と時刻に関わる関数とは？
その名の通り、日付データや時刻データに変換したり、一部だけ抽出したり、といったことを行うことが出来る関数です。
Snowflakeの日付と時刻に関する関数は、大きく以下の6種類に分かれています。
- 構築：各関数の引数に値を入れることで、新しく日付・時刻データを構築する関数
- 抽出：日付・時刻データから、年、月、日など一部の情報だけを抽出する関数
- 加算/減算：日付・時刻データに対して、引数に入れた値に応じて加算・減算した結果を返す関数
- 切り捨て：ある日付・時刻データから、秒以下を切り捨て、ある時間別に区切る、といったことが出来る関数
- 変換：元々別の型のデータを、日付・時刻データに変換するための関数
- タイムゾーン：タイムスタンプ型のデータのタイムゾーンを変換するための関数（
CONVERT_TIMEZONEのみ）
日付と時刻に関する関数の一覧が記載された公式Docは下記になります。
この記事では、このうちの「加算/減算・切り捨て」に関する関数について試してみたのでまとめてみます。
加算/減算
「加算/減算」に関しては、エイリアスに当たる関数を除けば以下に記載する4つが主な関数となります。
※ブログ投稿日である「2021年12月17日」をベースにdate_from_parts(2021, 12, 17)と表記してDATE型の値とし、各種関数を使ったサンプルを用いて説明していきます。
指定した値だけ月数を足して返すADD_MONTHS
第2引数に指定した値を、第1引数に入れたDATE型・TIMESTAMP型のデータの月に加算して返す関数です。月の最終日を指定した場合は、Snowflake側でよしなに最終日を28日～31日で調整して返してくれます。
- ブログ投稿日である「2021年12月17日」の1ヶ月後の値を返す
select add_months(date_from_parts(2021, 12, 17), 1);
- 12月の最終日である大晦日「2021年12月31日」の1ヶ月前の日付を返す（11月の最終日である2021年11月30日が返されます）
select add_months(date_from_parts(2021, 12, 31), -1);
指定した値の分だけ指定したdate_partに追加して返すDATEADD
第1引数でどの箇所に対して加算・減算するかのdate_partを指定し、第2引数で加算・減算する値を指定、第3引数で加算・減算対象のデータ・カラムを指定して、演算が行われた値を返す関数です。
- 「2021年12月17日」の3日後の値を返す
select dateadd(day,3,date_from_parts(2021, 12, 17));
- 「2021年12月17日」の1年前の値を返す
select dateadd(year,-1,date_from_parts(2021, 12, 17));
指定したdate_partで2つの値の差分を返すDATEDIFF
第1引数でどの箇所を用いた差を求めるかdate_partを指定し、第3引数から第2引数を引いた差分の値を返します。
- 「2021年12月17日」と「2021年12月31日」の日数差を返す
select datediff(day,date_from_parts(2021, 12, 17),date_from_parts(2021, 12, 31));
- 「2021年12月17日」と「2021年12月31日」の年数差を返す ※年に差はないため「0」を返します。
select datediff(year,date_from_parts(2021, 12, 17),date_from_parts(2021, 12, 31));
2つの日付データの月数差を数値で返すMONTHS_BETWEEN
月数差を求めたい2つのDATE型・TIMESTAMP型のデータを第1引数、第2引数に入れることで、月数差を返す関数です。第1引数-第2引数の結果が返ってきます。ポイントは、丸1ヶ月違いがあれば1を返すのですが、丸1ヶ月の差でなければ小数点で値を返します。
- 「2021年12月1日」と「2021年11月1日」の月数差を返す ※ちょうど1ヶ月異なるため、1を返します。
select months_between(date_from_parts(2021, 12, 17),date_from_parts(2021, 11, 17));
詳細なルールは下記の公式Docよりご確認ください。
切り捨て
「切り捨て」に関しては、エイリアスに当たる関数を除けば以下に記載する2つが主な関数となります。
※ブログ投稿日である「2021年12月17日0時30分」をベースにtimestamp_from_parts(2021, 12, 17, 0, 30, 00)と表記してTIMESTAMP型の値とし、各種関数を使ったサンプルを用いて説明していきます。
指定したdate_part未満を切り捨てた値を返すDATE_TRUNC
第2引数で指定したDATE型・TIMESTAMP型の値から、第1引数で指定したdate_part未満の値を切り捨てた値を返す関数です。一方が分まで持つTIMESTAMP型、もう一方が日までしか持たないDATE型、という状況でそのカラムを元にJOINしたいときなど、役に立つ関数です。
- 「2021年12月17日0時30分」のTIMESTAMP型のデータから、”時間”未満を切り捨てた値を返す ※分・秒の値が切り捨てられ0となります。
select date_trunc(hour,timestamp_from_parts(2021, 12, 17, 0, 30, 00));
- 「2021年12月17日0時30分」のTIMESTAMP型のデータから、”月”未満を切り捨てた値を返す ※日はすべて1となり、時・分・秒の値は切り捨てられ0となります。
select date_trunc(month,timestamp_from_parts(2021, 12, 17, 0, 30, 00));
一定の間隔で区切ったスライスを定義し値を分類して返すTIME_SLICE
第1引数で指定したDATE型・TIMESTAMP型の値に対し、第3引数で指定したdate_partで、第2引数で指定した値の間隔(スライス)で分けて分類した結果を返します。 例えば、上期を1月~6月・下期を7月～12月として分析したい時など、独自の間隔で分けて分析したい時に使える関数です。
また、第4引数にENDと指定することで、第1引数で指定した値が該当するスライスの最終日を返します。（デフォルト値がSTARTのため、第4引数に何も値を入れないと該当するスライスの開始日を返します。）
公式Docにもありますが、すべてのスライスは、1970年1月1日午前0時（1970-01-01 00：00：00）を基準にしております。この点だけご注意ください。
- 6ヶ月間隔のスライスで分けた時、「2021年12月17日」が該当するスライスの開始日と最終日を返す
select '2021-12-17'::date as "POSTDATE",
time_slice("POSTDATE", 6, 'MONTH', 'START') as "START OF SLICE",
time_slice("POSTDATE", 6, 'MONTH', 'END') as "END OF SLICE";
次回
Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の18日目では、「Snowflakeとdbtを併せて使うと何が良いのか？」というタイトルで執筆します。お楽しみに！
5
