Snowflakeで日付と時刻の関数を試してみた~構築・抽出編~ #SnowflakeDB

2021.12.16

※本エントリは、Snowflakeをより使いこなそう! Advent Calendar 2021の16日目の記事となります。

さがらです。

Snowflakeで扱うデータの中でも特に目にすることの多い、日付や時刻のデータを扱う関数について、試してみたので本記事でまとめてみます。※この記事では「構築・抽出」に関わる関数について書きます。

日付と時刻に関わる関数とは?

その名の通り、日付データや時刻データに変換したり、一部だけ抽出したり、といったことを行うことが出来る関数です。

Snowflakeの日付と時刻に関する関数は、大きく以下の6種類に分かれています。

  • 構築:各関数の引数に値を入れることで、新しく日付・時刻データを構築する関数
  • 抽出:日付・時刻データから、年、月、日など一部の情報だけを抽出する関数
  • 加算/減算:日付・時刻データに対して、引数に入れた値に応じて加算・減算した結果を返す関数
  • 切り捨て:ある日付・時刻データから、秒以下を切り捨て、ある時間別に区切る、といったことが出来る関数
  • 変換:元々別の型のデータを、日付・時刻データに変換するための関数
  • タイムゾーン:タイムスタンプ型のデータのタイムゾーンを変換するための関数(CONVERT_TIMEZONEのみ)

日付と時刻に関する関数の一覧が記載された公式Docは下記になります。

この記事では、このうちの「構築・抽出」に関する関数について試してみたのでまとめてみます。

構築

構築には3種類の関数があり、どれも引数に入れた値に応じて新しく日付・時刻データを構築する関数となっています。

使いどころとしては、年、月、日、それぞれが別カラムで定義されていた場合に一つにまとめて日付・時刻を表すデータ型に変換したい場合などがあげられます。

実際の関数としてはDATE_FROM_PARTSTIME_FROM_PARTSTIMESTAMP_FROM_PARTSの3種類があります。以下にDATE_FROM_PARTSを用いたサンプルを記載します。

DATE_FROM_PARTS関数は、引数に「年、月、日」の順番に整数値を入れることで、DATE型のデータとして値を返してくれる関数です。

以下の例では、このブログの投稿日でもある2021年12月16日をDATE型として表すように引数を設定したサンプルです。

select date_from_parts(2021, 12, 16);

抽出

抽出には多くの関数があるのですが、大まかに以下3つに分類されるので、分けて説明していきます。

  • 対象の日付・時刻データから一部のデータを抽出する
  • 該当する曜日、月の名称を返す
  • 引数の値に応じて該当する日付の値を返す

対象の日付・時刻データから一部のデータを抽出する

この分類には当てはまる関数は数が多いため、いくつか事例を持って説明していきます。どの関数でも返ってくる結果は同じのため、好みに合う書き方を採用するとよいと思います。

※すべてのサンプルで、上述したこのブログ投稿日をDATE型として返すdate_from_parts(2021, 12, 16)を使っております。実際のクエリにおいては、この関数が入る箇所にDATE型やTIMESTAMP型のカラムを入れることが多いと思います。

対象の日付・時刻データから「年」を抽出したい

  • DATE_PART関数を使う場合
select date_part(year, date_from_parts(2021, 12, 16));

  • EXTRACT関数を使う場合
select extract(year from date_from_parts(2021, 12, 16));

  • YEAR関数を使う場合
select year(date_from_parts(2021, 12, 16));

対象の日付・時刻データから「四半期」を抽出したい

※12月は第四四半期なので、「4」が返ってきます。

  • DATE_PART関数を使う場合
select date_part(quarter, date_from_parts(2021, 12, 16));

  • EXTRACT関数を使う場合
select extract(quarter from date_from_parts(2021, 12, 16));

  • QUARTER関数を使う場合
select quarter(date_from_parts(2021, 12, 16));

該当する曜日、月の名称を返す

該当する曜日、月の名称を返す関数として、DAYNAME関数とMONTHNAME関数があります。

DAYNAME関数は、引数で指定されたDATE型・TIMESTAMP型のデータから曜日を判定して返します。(Mon~Sunという表記で返します。)

select dayname(date_from_parts(2021, 12, 16));

MONTHNAME関数は、引数で指定されたDATE型・TIMESTAMP型のデータから月を判定して、英語表記の名称の先頭3文字を返します。(Jan~Decという表記で返します。)

select monthname(date_from_parts(2021, 12, 16));

引数の値に応じて該当する日付の値を返す

この分類には、LAST_DAYNEXT_DAYPREVIOUS_DAYの3種類の関数が含まれます。以下、それぞれ説明していきます。

指定されたdate_partの最終日を返すLAST_DAY関数

LAST_DAY関数は、第2引数に指定したdate_partの内容に基づいた最終日を返します。説明だけだとわかりづらいと思いますので例を用いて説明します。

  • ブログ投稿日(2021年12月16日)の月の最終日を知りたい
select last_day(date_from_parts(2021, 12, 16), month);

  • ブログ投稿日(2021年12月16日)の週の最終日を知りたい ※デフォルト設定では月曜日~日曜日、なので日曜日が週の最終日扱いとなっています。
select last_day(date_from_parts(2021, 12, 16), week);

指定した日付から次の〇曜日の日付を返すNEXT_DAY関数

NEXT_DAY関数は、第2引数に指定した曜日名に応じて、次にやってくる指定した曜日の日付を返します。

  • ブログ投稿日(2021年12月16日:木曜日)から見て、次の土曜日の日付を知りたい
select next_day(date_from_parts(2021, 12, 16), 'saturday');

指定した日付に対して直近の〇曜日の日付を返すPREVIOUS_DAY関数

PREVIOUS_DAY関数は、第2引数に指定した曜日名に応じて、指定した日付に対して直近で該当する日付を返します。

  • ブログ投稿日(2021年12月16日:木曜日)から見て、直近の土曜日の日付を知りたい
select previous_day(date_from_parts(2021, 12, 16), 'saturday');

次回

Snowflakeをより使いこなそう! Advent Calendar 2021、次回の17日目では、「Snowflakeで日付と時刻の関数を試してみた~加算減算・切り捨て編~」というタイトルで執筆します。お楽しみに!