日付と時刻のデータ型

このトピックでは、日付、時刻、タイムスタンプ(日付と時刻の組み合わせ)を管理するためにSnowflakeでサポートされているデータ型について説明します。また、日付、時刻、タイムスタンプの操作に使用される文字列定数でサポートされている形式についても説明します。

このトピックの内容:

データ型

DATE

Snowflakeは、日付を保存するための単一の DATE データ型をサポートしています(時間要素はなし)。DATE は、最も一般的な形式(YYYY-MM-DDDD-MON-YYYY など)の日付を受け入れます。さらに、受け入れられるすべてのタイムスタンプは、日付の有効な入力です。しかし、 TIME 情報は切り捨てられます。

DATETIME

DATETIME は、 TIMESTAMP_NTZのエイリアスです。

TIME

Snowflakeは、時刻を HH:MI:SS の形式で保存するための単一の TIME データ型をサポートしています。TIME は、 TIME(3)などの小数秒のオプションの精度パラメーターをサポートします。時間精度の範囲は、0(秒)から9(ナノ秒)までです。デフォルトの精度は9です。

すべての TIME 値は、 00:00:0023:59:59.999999999 の間でなければなりません。TIME は「wallclock」時間を内部的に保存し、 TIME 値に対するすべての操作はタイムゾーンを考慮せずに実行されます。

TIMESTAMP

SnowflakeのTIMESTAMP は、 TIMESTAMP_*バリエーションの1つに関連付けられたユーザー指定のエイリアスです。 TIMESTAMP が使用されるすべての操作では、関連する TIMESTAMP_*バリエーションが自動的に使用されます。 TIMESTAMP データ型はテーブルに保存されません。

TIMESTAMP に関連付けられている TIMESTAMP_*バリエーションは、 TIMESTAMP_TYPE_MAPPING セッションパラメーターで指定されます。デフォルトは TIMESTAMP_NTZです。

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflakeは、3種類のタイムスタンプをサポートしています。すべてのタイムスタンプのバリエーションと TIMESTAMP エイリアスは、小数秒のオプションの精度パラメーターをサポートします(例: TIMESTAMP(3))。タイムスタンプの精度は、0(秒)から9(ナノ秒)の範囲です。デフォルトの精度は9です。

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

TIMESTAMP_TZ

TIMESTAMP_TZ は、 UTC 時間と関連する タイムゾーンオフセット を内部に保存します。タイムゾーンが指定されていない場合、セッションタイムゾーンオフセットが使用されます。すべての操作は、各レコードに固有のタイムゾーンオフセットで実行されます。

TIMESTAMP_TZのエイリアス:

  • TIMESTAMPTZ

  • TIMESTAMP WITH TIME ZONE

注意

TIMESTAMP_TZ では、現在特定の値の作成時に、実際の タイムゾーン ではなく、特定のタイムゾーンの オフセット のみが保存されます。これは、 UTC で使用されない夏時間を扱う際に注意することが特に重要です。

たとえば、 TIMEZONE パラメーターを "America/Los_Angeles" に設定して、特定の年の1月に値を TIMESTAMP_TZ に変換すると、タイムゾーンオフセット -0800 が保存されます。後で値に6か月が追加されると、7月にロサンゼルスのオフセットが -0700 であっても、 -0800 オフセットが保持されます。これは、値が作成された後、実際のタイムゾーン情報("America/Los_Angeles")が利用できなくなるためです。次のコードサンプルは、この動作を示しています。

SELECT '2017-01-01 12:00:00'::TIMESTAMP_TZ;

-------------------------------------+
 '2017-01-01 12:00:00'::TIMESTAMP_TZ |
-------------------------------------+
 2017-01-01 12:00:00 -0800           |
-------------------------------------+

SELECT DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ);

--------------------------------------------------------+
 DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ) |
--------------------------------------------------------+
 2017-07-01 12:00:00 -0800                              |
--------------------------------------------------------+

タイムスタンプの例

異なるタイムスタンプを使用してテーブルを作成します。

-- First, use TIMESTAMP (mapped to 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 |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

-- Next, explicitly use one of the TIMESTAMP variations (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 |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | 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('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the time for January 2nd is 08:00 in Los Angeles (which is 16:00 in UTC)

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 08:00:00 -0800 |        8 |
+---------------------------------+----------+

-- Next, note that the times change with a different time zone

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 19:00:00 -0500 |       19 |
| Thu, 02 Jan 2014 11:00:00 -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('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that both times from different time zones are converted to the same "wallclock" time

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

TIMESTAMP_TZを使用します:

CREATE OR REPLACE TABLE ts_test(ts timestamp_tz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the January 1st record inherited the session time zone,
-- and "America/Los_Angeles" was converted into a numeric time zone offset

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+

サポートされているカレンダー

Snowflakeは、すべての日付とタイムスタンプにグレゴリオ暦を使用します。グレゴリオ暦は1582年に始まりますが、Snowflakeはユリウス暦と一致するように1582年以前の日付(または1582年以前の日付を含む計算)を 調整しない ため、前の年を認識します。

日付と時刻の形式

これらのデータ型はすべて、最も妥当であり曖昧でない日付、時刻、または日付と時刻の形式を受け入れます。手動で指定した日付と時刻の形式には、次の要素を含めることができます(大文字と小文字の区別なし)。

形式

説明

YYYY

年を表す4桁の数字。

YY

TWO_DIGIT_CENTURY_START セッションパラメーターによって制御される年を表す2桁の数字。例: 1980 に設定されている場合、 79 および 80 の値はそれぞれ 2079 および 1980 として解析されます。

MM

月を表す2桁の数字(01=1月など)。

MON

月の略称。

DD

日を表す2桁の数字(01から31)。

DY

曜日の略称。

HH24

時間を表す2桁の数字(00から23)。午前/午後は許可されて いません

HH12

時間を表す2桁の数字(01から12)。午前/午後は許可されています。

AMPM

午前(am)/午後(pm)。 HH12 で使用します。

MI

分を表す2桁の数字(00から59)。

SS

秒を表す2桁の数字(00から59)。

FF

精度0(秒)から9(ナノ秒)までの小数秒。例: FFFF0FF3FF9FF を指定することは、 FF9 (ナノ秒)と同等です。

TZH:TZMTZHTZMTZH

タイムゾーンの時間と分、 UTC との時差。記号の前に +/- を付けることができます。

注釈

日付のみの形式を使用する場合、関連付けられた時刻はその日の午前0時と見なされます。

日付と時刻形式の使用例

次の例では、「FF」を使用して、出力の小数秒フィールドに9桁を含める必要があることを示しています。

CREATE TABLE gilkey_temporary(t  TIMESTAMP, 
                              t_tz TIMESTAMP_TZ,
                              t_ntz TIMESTAMP_NTZ,
                              t_ltz TIMESTAMP_LTZ);
INSERT INTO gilkey_temporary (t, t_tz, t_ntz, t_ltz) VALUES (
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-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 gilkey_temporary;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T                             | T_TZ                          | T_NTZ                         | T_LTZ                         |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

日付と時刻の定数

定数 (別名 リテラル)は、固定データ値を指します。Snowflakeは、文字列定数を使用した固定の日付、時刻、またはタイムスタンプ値の指定をサポートしています。文字列定数は常に区切り文字で囲む必要があります。Snowflakeは、単一引用符を使用した文字列定数区切りをサポートしています。

例:

date '2010-09-14'
time '10:03:56'
timestamp '2009-09-15 10:59:43'

文字列は、次のパラメーターで設定されたデータ型の入力形式に基づいて、 DATE、 TIME、または TIMESTAMP の値として解析されます。

DATE

DATE_INPUT_FORMAT

TIME

TIME_INPUT_FORMAT

TIMESTAMP

TIMESTAMP_INPUT_FORMAT

例:テーブルの列に特定の日付を挿入します。

CREATE TABLE t1 (d1 DATE);

INSERT INTO t1 (d1) VALUES (DATE '2011-10-29');

間隔定数

間隔定数を使用して、日付、時刻、またはタイムスタンプに期間を追加または削除できます。間隔定数は、次の構文を持つ 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として定義することはできません)。間隔は、日付、時刻、タイムスタンプの計算でのみ使用できます。

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

INTERVAL キーワードは、次の日付と時刻部分を引数としてサポートします(大文字と小文字の区別なし)。

日付または時刻部分

略語/バリエーション

year

yyyyyyyyyyyryearsyrs

quarter

qqtrqtrsquarters

month

mmmonmonsmonths

week

wwkweekofyearwoywyweeks

day

ddddaysdayofmonth

hour

hhhhrhourshrs

minute

mmiminminutesmins

second

ssecsecondssecs

millisecond

msmsecmilliseconds

microsecond

ususecmicroseconds

nanosecond

nsnsecnanosecnsecondnanosecondsnanosecsnseconds

間隔の例

特定の日付に年間隔を追加します。

select to_date('2018-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-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_INTERVAL              |
|-------------------------------|
| 2020-12-28 08:08:01.325 -0800 |
+-------------------------------+

特定の日付に、略称の日付/時刻の部分表記を使用した複雑な間隔を追加します。

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.841 |
+-------------------------+

従業員情報のテーブルをクエリし、過去2年と3か月以内に雇用された従業員の名前を返します。

select name, hire_date from employees where hire_date > current_date - INTERVAL '2 y, 3 month';

tl という名前のテーブルから ts という名前のタイムスタンプ列をフィルターし、各戻り値に4秒を追加します。

select ts + INTERVAL '4 seconds' from t1 where ts > to_timestamp('2014-04-05 01:02:03');

日付の単純な演算

間隔定数を使用して日付、時刻、タイムスタンプに加算および減算することに加えて、Snowflakeは、 { + | - } <整数> の形式で DATE 値への基本的な加算および減算もサポートします。 <整数> は、加算/減算する日数の数値を指定します。

注釈

TIME および TIMESTAMP 値はまだ単純な演算をサポートしていません。

日付演算の例

特定の日付に1日を加算します。

select to_date('2018-04-15') + 1;

+---------------------------+
| TO_DATE('2018-04-15') + 1 |
|---------------------------|
| 2018-04-16                |
+---------------------------+

特定の日付から4日を減算します。

select to_date('2018-04-15') - 4;

+---------------------------+
| TO_DATE('2018-04-15') - 4 |
|---------------------------|
| 2018-04-11                |
+---------------------------+

employees という名前のテーブルをクエリし、365日以上雇用された退職者の名前を返します。

select name from employees where end_date > start_date + 365;