日付と時刻の関数

この関数ファミリーを使用して、 DATE/TIME/TIMESTAMP データを構築、変換、抽出、または変更できます。

このトピックの内容:

関数のリスト

サブカテゴリ

関数

注意

構築

DATE_FROM_PARTS

TIME_FROM_PARTS

TIMESTAMP_FROM_PARTS

抽出

DATE_PART

すべての日付と時刻の部分を受け入れます(詳細は次のセクションを参照)。

DAYNAME

EXTRACT

DATE_PART の代替。

HOUR / MINUTE / SECOND

DATE_PART の代替。

LAST_DAY

関連する日付部分を受け入れます(詳細は次のセクションを参照)。

MONTHNAME

NEXT_DAY

PREVIOUS_DAY

YEAR* / DAY* / WEEK* / MONTH / QUARTER

DATE_PART の代替。

加算/減算

ADD_MONTHS

DATEADD

関連する日付と時刻の部分を受け入れます(詳細は次のセクションを参照)。

DATEDIFF

関連する日付と時刻の部分を受け入れます(詳細は次のセクションを参照)。

TIMEADD

DATEADD のエイリアス。

TIMEDIFF

DATEDIFF のエイリアス。

TIMESTAMPADD

DATEADD のエイリアス。

TIMESTAMPDIFF

DATEDIFF のエイリアス。

切り捨て

DATE_TRUNC

関連する日付と時刻の部分を受け入れます(詳細は次のセクションを参照)。

TIME_SLICE

等間隔の開始に時間を「四捨五入」することができます。

TRUNC

DATE_TRUNC の代替。

変換

TO_DATE , DATE

TO_TIME , TIME

TO_TIMESTAMP / TO_TIMESTAMP_*

タイムゾーン

CONVERT_TIMEZONE

サポートされている日付と時刻の部分

特定の関数(および適切なエイリアスと代替)は、引数として日付または時刻の部分を受け入れます。次の2つの表には、これらの関数で使用できる部分(大文字と小文字を区別しない)が示されています。

日付部分

略語/バリエーション

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

year

y , yy , yyy , yyyy , yr , years , yrs

month

mm , mon , mons , months

day

d , dd , days, dayofmonth

dayofweek [1]

weekday , dow , dw

dayofweekiso [2]

weekday_iso , dow_iso , dw_iso

dayofyear

yearday , doy , dy

week [1]

w , wk , weekofyear , woy , wy

weekiso [2]

week_iso , weekofyeariso , weekofyear_iso

quarter

q , qtr , qtrs , quarters

yearofweek [1]

yearofweekiso [2]

[1] 使用方法の詳細については、次のセクションをご参照ください。カレンダーの週と曜日をSnowflakeが処理する方法について説明されています。

[2] 次のセクションの説明にあるように、 WEEK_START および WEEK_OF_YEAR_POLICY セッションパラメーターによって制御されません。

時刻の部分

略語/バリエーション

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

hour

h , hh , hr , hours , hrs

minute

m , mi , min , minutes , mins

second

s , sec , seconds , secs

millisecond

ms , msec , milliseconds

microsecond

us , usec , microseconds

nanosecond

ns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds

epoch_second

epoch , epoch_seconds

epoch_millisecond

epoch_milliseconds

epoch_microsecond

epoch_microseconds

epoch_nanosecond

epoch_nanoseconds

timezone_hour

tzh

timezone_minute

tzm

カレンダーの週と平日

Snowflakeの週関連機能の動作は、 WEEK_START および WEEK_OF_YEAR_POLICY セッションパラメーターによって制御されます。これらのパラメーターが相互作用する方法を理解する重要なポイントは、 ISO の週の概念です。

ISO の週

ISO 8601 標準(日付および時刻形式) で定義されているように、 ISO の週は常に月曜日から始まり、その週の木曜日を含む年に「属します」。これは、ある年の1日が別の年の週に属する可能性があることを意味します。

  • 1月上旬の場合、 WOY (年の週)値は52または53(つまり、その日は前年の最後の週に属す)になります。

  • 12月下旬の場合、 WOY の値は1になります(つまり、その日は翌年の最初の週に属す)。

Snowflakeは、週に関連する特別な一連の日付関数(および同等のデータ部分)を提供し、その動作は DAYOFWEEKISO 、 WEEKISO 、 YEAROFWEEKISO の ISO 週のセマンティクスと一致しています。

これらの関数(および日付部分)はセッションパラメーターを無視します(つまり、常に ISO セマンティクスに従います)。

週に関連する他のすべての日付関数の処理方法の詳細については、このトピックの次のセクションをご参照ください。

週の最初の日

ほとんどの週関連の機能は、 WEEK_START セッションパラメーターによってのみ制御されます。関数の結果は、パラメーターの設定方法に応じて異なります。

関数

パラメーターを 0 に設定(デフォルト/レガシー動作)

パラメーターを 1 - 7 に設定(月曜日~日曜日)

DAY および DAYOFWEEK

0 (日曜日)から 6 (土曜日)を返します。

1 (定義された週の最初の日)から 7 (定義された最初の日を基準とした最後の曜日)を返します。

DATE_TRUNCWEEK の部分)

入力週を切り捨てて月曜日に開始します。

入力週を切り捨てて、定義された週の最初の日に開始します。

LAST_DAYWEEK の部分)

入力週の日曜日を返します。

定義された週の最初の日からの相対的な入力週の最終日を返します。

DATEDIFFWEEK の部分)

月曜日から始まる週を使用して計算されます。

定義された週の最初の日から始まる週を使用して計算されます。

ちなみに

パラメーターのデフォルト値は 0 です。これは、従来のSnowflakeの動作(ISOのようなセマンティクス)を保持します。ただし、この値を変更して、関数の結果の動作を明示的に制御することを推奨します。受け取ったフィードバックに基づき、パラメーターを 1 に設定することが最も一般的なシナリオです。

今年の最初/最後の週

WEEK_OF_YEAR_POLICY セッションパラメーターは、 WEEK および YEAROFWEEK 関数の動作を制御します。パラメーターには2つの値を指定できます。

  • 0:影響を受ける週関連の機能は、 ISO のセマンティクスと同様のセマンティクスを使用します。セマンティクスでは、その週の少なくとも4日がその年にある場合、その週は特定の年に属します。つまり、すべての週には7日がありますが、1月の最初の日と12月の最後の日は異なる年の週に属する可能性があります。このため、 YEAROFWEEK 関数と YEAROFWEEKISO 関数の両方で、週が属する年を提供できます。

  • 1:1月1日は常に年の最初の週から始まり、12月31日は常に年の最後の週に始まります。これは、年の最初の週と最後の週の日数が7日未満になる可能性があることを意味します。

この動作は、 WEEK_START セッションパラメーターに設定された値によって制御されるように、週の開始日にも影響されます。

  • 0 , 1:この動作は、週が月曜日から始まる ISO 週のセマンティクスと同等です。

  • 27:「4日」のロジックは保持されますが、週の最初の日は異なります。

ちなみに

両方のパラメーターのデフォルト値は 0 で、これは従来のSnowflakeの動作(ISOのようなセマンティクス)を保持します。ただし、これらの値を変更して、関数の結果の動作を明示的に制御することを推奨します。受け取ったフィードバックに基づき、両方のパラメーターを 1 に設定することが最も一般的なシナリオです。

注釈

これらの例では、同じ関数のセットをクエリしますが、 WEEK_OF_YEAR_POLICY および WEEK_START セ ッションパラメーターに異なる値を設定して、関数の結果にどのように影響するかを示します。

週の最初の日の制御

次の2つの例に示すように、 WEEK_START を 0 (従来の動作)または 1 (月曜日)に設定しても、大きな影響はありません。

ALTER SESSION SET WEEK_START = 0;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   0 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   0 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

ALTER SESSION SET WEEK_START = 1;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   7 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   7 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
  1. WEEK_START を 0 に設定すると、日曜日の DOW は 0 です。

  2. WEEK_START を 1 に設定すると、日曜日の DOW は 7 です。

WEEK_START が月曜日以外の日に設定されている場合、結果は大幅に異なります。例えば、パラメーターを 3 (水曜日)に設定すると、週に関連するすべての関数(列3~8)の結果は変更されます。

ALTER SESSION SET WEEK_START = 3;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   3 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2016-12-31 | Sat |   4 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-01 | Sun |   5 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-02 | Mon |   6 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-03 | Tue |   7 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-04 | Wed |   1 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-01-05 | Thu |   2 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-12-30 | Sat |   4 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
| 2017-12-31 | Sun |   5 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

年の最初/最後の週の年と日を制御する

次の例では、両方のパラメーターを ISO のようなセマンティクスに従うように 0 に設定します(つまり、週は月曜日に始まり、すべての週は7日間です)。

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=0;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  52 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  52 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  52 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

次の例は、 WEEK_OF_YEAR_POLICY を 0 に設定したまま、 WEEK_START を 3 (水曜日)に変更した場合の影響を示しています。

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  53 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |  53 |         1 | 2016 |      2017 |
| 2017-01-03 | Tue |  53 |         1 | 2016 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  • 2016年は、53週間になりました(52ではない)。

  • 2017年1月1日のWOY は、53週に変化します(52から)。

  • 2017年1月2日と3日のWOY は、53週に変化します(1から)。

  • 2017年1月2日と3日のYOW は、2016に変化します(2017から)。

  • WOY (ISO)と YOW (ISO)は、パラメーターの変更による影響を受けません。

最後の2つの例では、 WEEK_OF_YEAR_POLICY を 1 に設定し、 WEEK_START を最初に 1 (月曜日)、次に 3 (水曜日)に設定します。

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=1, WEEK_START=1;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   2 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   2 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

alter session set week_of_year_policy=1, week_start=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  1. WEEK_OF_YEAR_POLICY を 1 に設定し、 WEEK_START を 1 (月曜日)に設定した場合:

    • 2017-01-01 のWOY は、 1 です。

    • 1週は1日で構成されています。

    • 2週は Mon から始まります。

    これは、受け取ったフィードバックに基づく最も一般的な使用シナリオです。

  2. WEEK_OF_YEAR_POLICY を 1 に、 WEEK_START を 3 (水曜日)に設定した場合:

    • 2017-01-01のWOY はまだ 1 です。

    • 1週は3日で構成されています。

    • 2週は Wed から始まります。

どちらの例でも、 WOY (ISO)と YOW (ISO)はパラメーターの変更の影響を受けません。