日付と時刻のデータ型¶
Snowflakeは、日付、時刻、タイムスタンプ(日付と時刻の組み合わせ)を管理するデータ型をサポートしています。Snowflakeは、日付、時刻、タイムスタンプの操作に使用される文字列定数もサポートします。
このトピックの内容:
データ型¶
Snowflake は以下の日付と時刻のデータ型をサポートしています。
注釈
DATE と TIMESTAMP のデータの場合、Snowflakeは、1582と9999の間の年を使用することをお勧めします。Snowflakeは、この範囲外の数年を受け入れますが、 グレゴリオ暦の制限 のため、1582より前の年は避ける必要があります。
DATE¶
Snowflakeは、日付を保存するための単一の DATE データ型をサポートしています(時間要素はなし)。
DATE は、最も一般的な形式(YYYY-MM-DD
、 DD-MON-YYYY
など)の日付を受け入れます。
さらに、受け入れられるすべての TIMESTAMP 値は、日付の有効な入力です。しかし、 TIME 情報は切り捨てられます。
DATETIME¶
DATETIME は、 TIMESTAMP_NTZのエイリアスです。
TIME¶
Snowflakeは、時刻を HH:MI:SS
の形式で保存するための単一の TIME データ型をサポートしています。
TIME は、小数秒のオプションの精度パラメーター(例: TIME(3)
)をサポートします。時間精度の範囲は、0(秒)から9(ナノ秒)までです。デフォルトの精度は9です。
すべての TIME 値は、 00:00:00
~ 23:59:59.999999999
の間でなければなりません。TIME は「wallclock」時間を内部的に保存し、 TIME 値に対するすべての操作はタイムゾーンを考慮せずに実行されます。
TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ¶
Snowflakeは、3種類のタイムスタンプをサポートしています。
- TIMESTAMP_LTZ:
TIMESTAMP_LTZ は、 UTC 時間を指定された精度で内部に保存します。ただし、すべての操作は、 TIMEZONE セッションパラメーターによって制御される現在のセッションのタイムゾーンで実行されます。
TIMESTAMP_LTZのエイリアス:
TIMESTAMPLTZ
TIMESTAMP WITH LOCAL TIME ZONE
- TIMESTAMP_NTZ:
TIMESTAMP_NTZ は、「wallclock」時間を指定された精度で内部に保存します。すべての操作は、タイムゾーンを考慮せずに実行されます。
出力形式にタイムゾーンが含まれる場合、 UTC インジケーター(
Z
)が表示されます。TIMESTAMP_NTZ は、 TIMESTAMP のデフォルトです。
TIMESTAMP_NTZのエイリアス:
TIMESTAMPNTZ
TIMESTAMP WITHOUT TIME ZONE
DATETIME
- TIMESTAMP_TZ:
TIMESTAMP_TZ は、 UTC 時間と関連する タイムゾーンオフセット を内部に保存します。タイムゾーンが指定されていない場合、セッションタイムゾーンオフセットが使用されます。すべての操作は、各レコードに固有のタイムゾーンオフセットで実行されます。
TIMESTAMP_TZのエイリアス:
TIMESTAMPTZ
TIMESTAMP WITH TIME ZONE
TIMESTAMP_TZ 値は、 UTC の時間に基づいて比較されます。たとえば、次の異なるタイムゾーンの異なる時間の比較では、2つの値の UTC 時間が等しいため、 TRUE が返されます。
SELECT '2024-01-01 00:00:00 +0000'::TIMESTAMP_TZ = '2024-01-01 01:00:00 +0100'::TIMESTAMP_TZ;
注意
TIMESTAMP_TZ では、現在特定の値の作成時に、実際の タイムゾーン ではなく、特定のタイムゾーンの オフセット のみが保存されます。これは、 UTC で使用されない夏時間を扱う際に注意することが特に重要です。
たとえば、 TIMEZONE パラメーターを "America/Los_Angeles"
に設定して、特定の年の1月に値を TIMESTAMP_TZ に変換すると、タイムゾーンオフセット -0800
が保存されます。後で値に6か月が追加されると、7月にロサンゼルスのオフセットが -0700
であっても、 -0800
オフセットが保持されます。これは、値が作成された後、実際のタイムゾーン情報("America/Los_Angeles"
)が利用できなくなるためです。次のコードサンプルは、この動作を示しています。
SELECT '2024-01-01 12:00:00'::TIMESTAMP_TZ;
+-------------------------------------+
| '2024-01-01 12:00:00'::TIMESTAMP_TZ |
|-------------------------------------|
| 2024-01-01 12:00:00.000 -0800 |
+-------------------------------------+
SELECT DATEADD(MONTH, 6, '2024-01-01 12:00:00'::TIMESTAMP_TZ);
+--------------------------------------------------------+
| DATEADD(MONTH, 6, '2024-01-01 12:00:00'::TIMESTAMP_TZ) |
|--------------------------------------------------------|
| 2024-07-01 12:00:00.000 -0800 |
+--------------------------------------------------------+
TIMESTAMP¶
Snowflake内の TIMESTAMP は、 TIMESTAMP_* バリエーションの1つに関連付けられたユーザー指定のエイリアスです。TIMESTAMP が使用されるすべての操作では、関連する TIMESTAMP_* バリエーションが自動的に使用されます。TIMESTAMP データ型はテーブルに保存されません。
TIMESTAMP に関連付けられている TIMESTAMP_* バリエーションは、 TIMESTAMP_TYPE_MAPPING セッションパラメーターで指定されます。デフォルトは TIMESTAMP_NTZ です。
すべてのタイムスタンプのバリエーションと TIMESTAMP エイリアスは、小数秒のオプションの精度パラメーターをサポートします(例: TIMESTAMP(3)
)。タイムスタンプの精度は、0(秒)から9(ナノ秒)の範囲です。デフォルトの精度は9です。
タイムスタンプの例¶
これらの例では、異なるタイムスタンプを使ってテーブルを作成しています。
まず、 TIMESTAMP 列(TIMESTAMP_NTZ にマップされる)を持つテーブルを作成します。
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;
CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP);
DESC TABLE ts_test;
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS | TIMESTAMP_NTZ(9) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL | NULL |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
次に、 TIMESTAMP バリエーション(TIMESTAMP_LTZ)のいずれかを明示的に使用します。
CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);
DESC TABLE ts_test;
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS | TIMESTAMP_LTZ(9) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL | NULL |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
異なるタイムゾーンで TIMESTAMP_LTZ を使用します。
CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
このクエリでは、1月2日のロサンゼルスの時刻は08:00(UTC では16:00)です。
SELECT ts, hour(ts) FROM ts_test;
+-------------------------------+----------+
| TS | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 | 16 |
| 2024-01-02 08:00:00.000 -0800 | 8 |
+-------------------------------+----------+
次に、タイムゾーンが異なると時間が変わることに注意します。
ALTER SESSION SET TIMEZONE = 'America/New_York';
SELECT ts, HOUR(ts) FROM ts_test;
+-------------------------------+----------+
| TS | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 19:00:00.000 -0500 | 19 |
| 2024-01-02 11:00:00.000 -0500 | 11 |
+-------------------------------+----------+
テーブルを作成して TIMESTAMP_NTZ を使用します。
CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_NTZ);
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
異なるタイムゾーンの時刻は、どちらも同じ「壁時計」の時刻に変換されることに注意してください。
SELECT ts, HOUR(ts) FROM ts_test;
+-------------------------+----------+
| TS | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 | 16 |
| 2024-01-02 16:00:00.000 | 16 |
+-------------------------+----------+
次に、セッションのタイムゾーンを変更しても、結果には影響しないことに注意してください。
ALTER SESSION SET TIMEZONE = 'America/New_York';
SELECT ts, HOUR(ts) FROM ts_test;
+-------------------------+----------+
| TS | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 | 16 |
| 2024-01-02 16:00:00.000 | 16 |
+-------------------------+----------+
テーブルを作成して TIMESTAMP_TZ を使用します。
CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_TZ);
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
1月1日の記録はセッションのタイムゾーンを継承し、「America/Los_Angeles 」は数値のタイムゾーンオフセットに変換されたことに注意してください。
SELECT ts, HOUR(ts) FROM ts_test;
+-------------------------------+----------+
| TS | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 | 16 |
| 2024-01-02 16:00:00.000 +0000 | 16 |
+-------------------------------+----------+
次に、セッションのタイムゾーンを変更しても、結果には影響しないことに注意してください。
ALTER SESSION SET TIMEZONE = 'America/New_York';
SELECT ts, HOUR(ts) FROM ts_test;
+-------------------------------+----------+
| TS | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 | 16 |
| 2024-01-02 16:00:00.000 +0000 | 16 |
+-------------------------------+----------+
サポートされているカレンダー¶
Snowflakeは、すべての日付とタイムスタンプにグレゴリオ暦を使用します。グレゴリオ暦は1582年に始まりますが、Snowflakeはユリウス暦と一致するように1582年以前の日付(または1582年以前の日付を含む計算)を 調整しない ため、前の年を認識します。 UUUU
形式要素は負の年をサポートしています。
日付と時刻の形式¶
これらのデータ型はすべて、最も妥当であり曖昧でない日付、時刻、または日付と時刻の形式を受け入れます。 自動的に形式を検出するように構成されている 場合に、Snowflakeが認識する形式については、 AUTO 検出でサポートされている形式 をご参照ください。
日付と時刻の形式を手動で指定する こともできます。形式を指定するときは、次のテーブルに示す大文字と小文字を区別しない要素を使用できます。
形式要素 |
説明 |
---|---|
|
年を表す4桁の数字。 |
|
2桁の年号で、 TWO_DIGIT_CENTURY_START セッション・パラメーターで制御されます。例えば、 |
|
2桁の月( |
|
完全な月名または短縮された月名。 |
|
完全な月名。 |
|
月の2桁の日( |
|
曜日の略称。 |
|
時間を表す2桁の数字( |
|
時間を表す2桁の数字( |
|
午前( |
|
分を表す2桁の数字( |
|
秒を表す2桁目の数字( |
|
|
|
タイムゾーンの時間と分、 UTC との時差。記号の前に |
|
ISO 形式 の4桁の年。これは BCE 年の場合は負になります。 |
注釈
日付のみの形式を使用する場合、関連付けられた時刻はその日の午前0時と見なされます。
二重引用符で囲まれた形式または上記の要素以外の形式のものは、解釈されることなく解析/フォーマットされます。
有効な範囲、桁数、ベストプラクティスの詳細については、 日付、時刻、およびタイムスタンプ形式の使用に関する追加情報 をご参照ください。
日付と時刻形式の使用例¶
次の例では、「 FF
」を使用して、出力の小数秒フィールドに9桁を含める必要があることを示しています。
CREATE TABLE timestamp_demo_table(
t TIMESTAMP,
t_tz TIMESTAMP_TZ,
t_ntz TIMESTAMP_NTZ,
t_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (t, t_tz, t_ntz, t_ltz) VALUES (
'2024-03-12 01:02:03.123456789',
'2024-03-12 01:02:03.123456789',
'2024-03-12 01:02:03.123456789',
'2024-03-12 01:02:03.123456789');
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SELECT t, t_tz, t_ntz, t_ltz
FROM timestamp_demo_table;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T | T_TZ | T_NTZ | T_LTZ |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
日付と時刻の定数¶
定数 (別名 リテラル)は、固定データ値を指します。Snowflakeは、文字列定数を使用した固定の日付、時刻、またはタイムスタンプ値の指定をサポートしています。文字列定数は常に区切り文字で囲む必要があります。Snowflakeは、単一引用符を使用した文字列定数区切りをサポートしています。
例:
DATE '2024-08-14'
TIME '10:03:56'
TIMESTAMP '2024-08-15 10:59:43'
文字列は、次のパラメーターで設定されたデータ型の入力形式に基づいて、 DATE、 TIME、または TIMESTAMP の値として解析されます。
- DATE:
- TIME:
- TIMESTAMP:
例:テーブルの列に特定の日付を挿入します。
CREATE TABLE t1 (d1 DATE);
INSERT INTO t1 (d1) VALUES (DATE '2024-08-15');
間隔定数¶
間隔定数を使用して、日付、時刻、またはタイムスタンプに期間を追加または削除できます。間隔定数は、次の構文を持つ INTERVAL キーワードを使用して実装されます。
{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'
すべての文字列定数と同様に、Snowflakeでは間隔定数を区切るために一重引用符が必要です。
INTERVAL キーワードは、もう1つの整数と、オプションで1つ以上の日付または時刻部分をサポートします。例:
INTERVAL '1 year'
は、1年を表します。INTERVAL '4 years, 5 months, 3 hours'
は4年5カ月と3時間を表しています。
日付または時刻の部分が指定されていない場合、間隔は秒を表します(例: INTERVAL '2'
は、 INTERVAL '2 seconds'
と同じです)。これは、日付演算を実行するためのデフォルトの時間単位とは異なります。詳細については、 日付の単純な演算 をご参照ください。
サポートされている日付と時刻部分のリストについては、このトピック内の サポートされている間隔の日付と時刻部分 をご参照ください。
注釈
間隔の増分の順序は重要です。増分は、リストされている順序で加算または減算されます。例:
INTERVAL '1 year, 1 day'
最初に1年、次に1日を加算/減算します。INTERVAL '1 day, 1 year'
最初に1日、次に1年を加算/減算します。
これは、うるう年などのカレンダーイベントの計算に影響を与える可能性があります。
SELECT TO_DATE ('2019-02-28') + INTERVAL '1 day, 1 year';
+---------------------------------------------------+ | TO_DATE ('2019-02-28') + INTERVAL '1 DAY, 1 YEAR' | |---------------------------------------------------| | 2020-03-01 | +---------------------------------------------------+
SELECT TO_DATE ('2019-02-28') + INTERVAL '1 year, 1 day';
+---------------------------------------------------+ | TO_DATE ('2019-02-28') + INTERVAL '1 YEAR, 1 DAY' | |---------------------------------------------------| | 2020-02-29 | +---------------------------------------------------+
INTERVAL は、データ型ではありません(テーブル列をデータ型 INTERVAL として定義することはできません)。間隔は、日付、時刻、タイムスタンプの計算でのみ使用できます。
SQL 変数 で区間を使うことはできません。例えば、次のクエリはエラーを返します。
SET v1 = '1 year'; SELECT TO_DATE('2023-04-15') + INTERVAL $v1;
サポートされている間隔の日付と時刻部分¶
INTERVAL キーワードは、次の日付と時刻部分を引数としてサポートします(大文字と小文字の区別なし)。
日付または時刻部分 |
略語/バリエーション |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
間隔の例¶
特定の日付に年間隔を追加します。
SELECT TO_DATE('2023-04-15') + INTERVAL '1 year';
+-------------------------------------------+
| TO_DATE('2023-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2024-04-15 |
+-------------------------------------------+
特定の時間に3時間18分のインターバルを追加します。
SELECT TO_TIME('04:15:29') + INTERVAL '3 hours, 18 minutes';
+------------------------------------------------------+
| TO_TIME('04:15:29') + INTERVAL '3 HOURS, 18 MINUTES' |
|------------------------------------------------------|
| 07:33:29 |
+------------------------------------------------------+
CURRENT_TIMESTAMP 関数の出力に複雑な間隔を追加します。
SELECT CURRENT_TIMESTAMP + INTERVAL
'1 year, 3 quarters, 4 months, 5 weeks, 6 days, 7 minutes, 8 seconds,
1000 milliseconds, 4000000 microseconds, 5000000001 nanoseconds'
AS complex_interval1;
+-------------------------------+
| COMPLEX_INTERVAL1 |
|-------------------------------|
| 2026-11-07 18:07:19.875000001 |
+-------------------------------+
特定の日付に、略称の日付/時刻の部分表記を使用した複雑な間隔を追加します。
SELECT TO_DATE('2025-01-17') + INTERVAL
'1 y, 3 q, 4 mm, 5 w, 6 d, 7 h, 9 m, 8 s,
1000 ms, 445343232 us, 898498273498 ns'
AS complex_interval2;
+-------------------------------+
| COMPLEX_INTERVAL2 |
|-------------------------------|
| 2027-03-30 07:31:32.841505498 |
+-------------------------------+
従業員情報のテーブルをクエリし、過去2年と3か月以内に雇用された従業員の名前を返します。
SELECT name, hire_date
FROM employees
WHERE hire_date > CURRENT_DATE - INTERVAL '2 y, 3 month';
t1
という名前のテーブルから ts
という名前の TIMESTAMP 列をフィルターし、各戻り値に4秒を追加します。
SELECT ts + INTERVAL '4 seconds'
FROM t1
WHERE ts > TO_TIMESTAMP('2024-04-05 01:02:03');
日付の単純な演算¶
間隔定数を使用して日付、時刻、タイムスタンプに加算および減算することに加えて、Snowflakeは、 { + | - }
integer
の形式で DATE 値への基本的な加算および減算もサポートします。 integer
は、加算/減算する日数の数値を指定します。
注釈
TIME および TIMESTAMP 値はまだ単純な演算をサポートしていません。
日付演算の例¶
指定した日付に1日を追加します。
SELECT TO_DATE('2024-04-15') + 1;
+---------------------------+
| TO_DATE('2024-04-15') + 1 |
|---------------------------|
| 2024-04-16 |
+---------------------------+
特定の日付から4日引きます。
SELECT TO_DATE('2024-04-15') - 4;
+---------------------------+
| TO_DATE('2024-04-15') - 4 |
|---------------------------|
| 2024-04-11 |
+---------------------------+
employees
という名前のテーブルをクエリし、365日以上雇用された退職者の名前を返します。
SELECT name
FROM employees
WHERE end_date > start_date + 365;