Snowflakeで日付と時刻の関数を試してみた～加算減算・切り捨て編～ #SnowflakeDB

アドベントカレンダー2021

#Snowflake

さがら

2021.12.17

この記事は公開されてから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";