日付と時刻の値の操作¶

日付と時刻の計算は、分析とデータマイニングで最も広く使用され、最も重要な計算の1つです。このトピックでは、一般的な日付と時刻のクエリと計算の実用的な例を示します。

このトピックの内容:

日付とタイムスタンプのロード¶

このセクションでは、日付とタイムスタンプの値をロードする例と、これらの値をロードする際のタイムゾーンに関する考慮事項について説明します。

タイムゾーンがないタイムスタンプのロード¶

次の例では、 TIMESTAMP_TYPE_MAPPING パラメーターは TIMESTAMP_LTZ （ローカルタイムゾーン）に設定されています。 TIMEZONE パラメーターが America/Chicago に設定されている場合、入力されるタイムスタンプにタイムゾーンが指定されていない場合、Snowflakeは、タイムスタンプが TIMEZONE パラメーターに設定されたタイムゾーンのローカル時間を表すものとして、これらの文字列をロードします。

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-05-01 00:00:00.000');

SELECT * FROM time;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-05-01 00:00:00.000 -0500 |
+-------------------------------+

タイムゾーンが付与されたタイムスタンプのロード¶

次の例では、 TIMESTAMP_TYPE_MAPPING パラメーターは TIMESTAMP_LTZ （ローカルタイムゾーン）に設定されています。 TIMEZONE パラメーターが America/Chicago に設定されている場合、受信タイムスタンプに異なるタイムゾーンが指定されている場合、Snowflakeは文字列を America/Chicago 時間の文字列にロードします。

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-04-30 19:00:00.000 -0800');

SELECT * FROM time;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-04-30 22:00:00.000 -0500 |
+-------------------------------+

別のタイムゾーンへのタイムスタンプの変換¶

次の例では、タイムゾーンデータがない一連のタイムスタンプ値が保存されます。タイムスタンプは UTC 時間でロードされ、他のタイムゾーンに変換されます。

ALTER SESSION SET TIMEZONE = 'UTC';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';

CREATE OR REPLACE TABLE utctime (ntz TIMESTAMP_NTZ);
INSERT INTO utctime VALUES ('2024-05-01 00:00:00.000');

Copy

SELECT * FROM utctime;

Copy

+-------------------------+
| NTZ                     |
|-------------------------|
| 2024-05-01 00:00:00.000 |
+-------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::TIMESTAMP_LTZ AS ChicagoTime
  FROM utctime;

Copy

+---------------------------+
| CHICAGOTIME               |
|---------------------------|
| 2024-04-30 19:00:00 +0000 |
+---------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::TIMESTAMP_LTZ AS LATime
  FROM utctime;

Copy

+---------------------------+
| LATIME                    |
|---------------------------|
| 2024-04-30 17:00:00 +0000 |
+---------------------------+

テーブルの日付列への有効な日付文字列の挿入¶

この例では、 DATE 列に値を挿入します。

CREATE OR REPLACE TABLE my_table(id INTEGER, date1 DATE);
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2024.07.23', 'YYYY.MM.DD'));
INSERT INTO my_table(id) VALUES (2);

Copy

SELECT id, date1
  FROM my_table
  ORDER BY id;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2024-07-23 |
|  2 | NULL       |
+----+------------+

TO_DATE 関数は、 TIMESTAMP 値および TIMESTAMP 形式の文字列も受け入れますが、時間情報（時間、分など）は破棄します。

INSERT INTO my_table(id, date1) VALUES
  (3, TO_DATE('2024.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
  (4, TO_TIMESTAMP('2024.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));

Copy

SELECT id, date1
  FROM my_table
  WHERE id >= 3;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2024-02-20 |
|  4 | 2024-02-24 |
+----+------------+

時刻のみで定義された DATE を挿入する場合、デフォルトの日付は1970年1月1日です。

INSERT INTO my_table(id, date1) VALUES
  (5, TO_DATE('11:20:30', 'hh:mi:ss'));

Copy

SELECT id, date1
  FROM my_table
  WHERE id = 5;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

DATE の値を取得する際、 TIMESTAMP の値としてフォーマットすることができます。

SELECT id,
       TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
  FROM my_table
  ORDER BY id;

Copy

+----+----------------------+
| ID | DATE1                |
|----+----------------------|
|  1 | 23-Jul-2024 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2024 00:00:00 |
|  4 | 24-Feb-2024 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+

現在の日付および時刻の取得¶

現在の日付を DATE 値として取得します。

SELECT CURRENT_DATE();

Copy

現在の日付および時刻を TIMESTAMP 値として取得します。

SELECT CURRENT_TIMESTAMP();

Copy

日付および曜日の取得¶

EXTRACT 関数を使用して、現在の曜日を数値として取得します。

SELECT EXTRACT('dayofweek', CURRENT_DATE());

Copy

注釈

dayofweek_iso 部分は、 ISO-8601データ要素と交換形式の標準に従います。この関数は、曜日を1-7の範囲の整数値として返します。1は月曜日を表します。
他のいくつかのシステムとの互換性のために、 dayofweek 部分は UNIX 標準に従います。この関数は、曜日を整数値として0-6の範囲で返します。0は日曜日を表します。

TO_VARCHAR または DECODE 関数を使用して、現在の曜日を文字列として取得します。

現在の日付の短い英語名（例えば「Sun」、「Mon」など）を返すクエリを実行します：

SELECT TO_VARCHAR(CURRENT_DATE(), 'dy');

Copy

明示的に指定された現在の日付の曜日名を返すクエリを実行します。

SELECT DECODE(EXTRACT('dayofweek_iso', CURRENT_DATE()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday') AS weekday_name;

Copy

日付および時刻の一部の取得¶

DATE_PART 関数を使用して、現在の日付および時刻のさまざまな部分を取得します。

現在の月日を問い合わせるクエリ：

SELECT DATE_PART(day, CURRENT_TIMESTAMP());

Copy

今年度のクエリ：

SELECT DATE_PART(year, CURRENT_TIMESTAMP());

Copy

当月のクエリ：

SELECT DATE_PART(month, CURRENT_TIMESTAMP());

Copy

現在の時間を問い合わせるクエリ：

SELECT DATE_PART(hour, CURRENT_TIMESTAMP());

Copy

現在の分を問い合わせるクエリ：

SELECT DATE_PART(minute, CURRENT_TIMESTAMP());

Copy

現在の秒数を問い合わせるクエリ：

SELECT DATE_PART(second, CURRENT_TIMESTAMP());

Copy

EXTRACT 関数を使用して、現在の日付および時刻のさまざまな部分を取得します。

現在の月日を問い合わせるクエリ：

SELECT EXTRACT('day', CURRENT_TIMESTAMP());

Copy

今年度のクエリ：

SELECT EXTRACT('year', CURRENT_TIMESTAMP());

Copy

当月のクエリ：

SELECT EXTRACT('month', CURRENT_TIMESTAMP());

Copy

現在の時間を問い合わせるクエリ：

SELECT EXTRACT('hour', CURRENT_TIMESTAMP());

Copy

現在の分を問い合わせるクエリ：

SELECT EXTRACT('minute', CURRENT_TIMESTAMP());

Copy

現在の秒数を問い合わせるクエリ：

SELECT EXTRACT('second', CURRENT_TIMESTAMP());

Copy

このクエリは、現在の日付と時刻の様々な日付と時刻の部分を持つ表形式の出力を返します。

SELECT month(CURRENT_TIMESTAMP()) AS month,
       day(CURRENT_TIMESTAMP()) AS day,
       hour(CURRENT_TIMESTAMP()) AS hour,
       minute(CURRENT_TIMESTAMP()) AS minute,
       second(CURRENT_TIMESTAMP()) AS second;

Copy

+-------+-----+------+--------+--------+
| MONTH | DAY | HOUR | MINUTE | SECOND |
|-------+-----+------+--------+--------|
|     8 |  28 |    7 |     59 |     28 |
+-------+-----+------+--------+--------+

ビジネスカレンダーの日付および時刻の計算¶

DATE_TRUNC 関数を使用して、月の最初の日を DATE 値として取得します。たとえば、現在の月の最初の日を取得します。

SELECT DATE_TRUNC('month', CURRENT_DATE());

Copy

DATEADD および DATE_TRUNC 関数を使用して、今月の最終日を DATE 値として取得します。

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('month', DATEADD(day, 31, DATE_TRUNC('month',CURRENT_DATE()))));

Copy

代替オプションとして、次の例では、 DATE_TRUNC は現在の月の初めを取得し、1か月を追加して翌月の初めを取得し、1日を減算して現在の月の最終日を決定します。

SELECT DATEADD('day',
               -1,
               DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())));

Copy

前月の最終日を DATE 値として取得します。

SELECT DATEADD(day,
               -1,
               DATE_TRUNC('month', CURRENT_DATE()));

Copy

現在の月の短い英語名（例えば「Jan」、「Dec」など）を取得します。

SELECT TO_VARCHAR(CURRENT_DATE(), 'Mon');

Copy

明示的に指定された月名を用いて現在の月名を取得します。

SELECT DECODE(EXTRACT('month', CURRENT_DATE()),
         1, 'January',
         2, 'February',
         3, 'March',
         4, 'April',
         5, 'May',
         6, 'June',
         7, 'July',
         8, 'August',
         9, 'September',
         10, 'October',
         11, 'November',
         12, 'December');

Copy

現在の週の月曜日の日付を取得します。

SELECT DATEADD('day',
               (EXTRACT('dayofweek_iso', CURRENT_DATE()) * -1) + 1,
               CURRENT_DATE());

Copy

現在の週の金曜日の日付を取得します。

SELECT DATEADD('day',
               (5 - EXTRACT('dayofweek_iso', CURRENT_DATE())),
               CURRENT_DATE());

Copy

DATE_PART 関数を使用して、今月の最初の月曜日の日付を取得します。

SELECT DATEADD(day,
               MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', CURRENT_DATE())), 7),
               DATE_TRUNC('month', CURRENT_DATE()));

Copy

注釈

上記のクエリでは、 7 + 1 の 1 値は月曜日に変換されます。最初の火曜日、水曜日などの日付を取得するには、それぞれ 2 、 3 、 7 を Sunday などに置き換えます。

現在の年の初日を DATE 値として取得します。

SELECT DATE_TRUNC('year', CURRENT_DATE());

Copy

現在の年の最後の日を DATE 値として取得します。

SELECT DATEADD('day',
               -1,
               DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())));

Copy

前年の最終日を DATE 値として取得します。

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('year',CURRENT_DATE()));

Copy

現在の四半期の初日を DATE 値として取得します。

SELECT DATE_TRUNC('quarter', CURRENT_DATE());

Copy

現在の四半期の最終日を DATE 値として取得します。

SELECT DATEADD('day',
               -1,
               DATEADD('month', 3, DATE_TRUNC('quarter', CURRENT_DATE())));

Copy

当日の真夜中の日付とタイムスタンプを取得します。

SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP());

Copy

+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700        |
+----------------------------------------+

日付および時刻の値の増加¶

DATEADD 関数を使用して、日付と時刻の値をインクリメントします。

現在の日付に2年を追加します。

SELECT DATEADD(year, 2, CURRENT_DATE());

Copy

現在の日付に2日を追加します。

SELECT DATEADD(day, 2, CURRENT_DATE());

Copy

現在の日時に2時間を追加します。

SELECT DATEADD(hour, 2, CURRENT_TIMESTAMP());

Copy

現在の日付と時刻に2分を追加します。

SELECT DATEADD(minute, 2, CURRENT_TIMESTAMP());

Copy

現在の日付と時刻に2秒を追加します。

SELECT DATEADD(second, 2, CURRENT_TIMESTAMP());

Copy

有効な文字列を日付、時刻、またはタイムスタンプへの変換¶

ほとんどのユースケースでは、Snowflakeは文字列としてフォーマットされた日付とタイムスタンプの値を正しく処理します。文字列ベースの比較などの特定の場合、または結果がセッションパラメーターで設定されたものとは異なるタイムスタンプ形式に依存する場合、予期しない結果を避けるために値を目的の形式に明示的に変換することを推奨します。

たとえば、明示的なキャストなしで、文字列値を比較すると、文字列ベースの結果が生成されます。

CREATE OR REPLACE TABLE timestamps(timestamp1 STRING);

INSERT INTO timestamps VALUES
  ('Fri, 05 Apr 2013 00:00:00 -0700'),
  ('Sat, 06 Apr 2013 00:00:00 -0700'),
  ('Sat, 01 Jan 2000 00:00:00 -0800'),
  ('Wed, 01 Jan 2020 00:00:00 -0800');

Copy

以下のクエリは、明示的なキャストなしで比較を実行します。

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

Copy

+------------+
| TIMESTAMP1 |
|------------|
+------------+

次のクエリは、 DATE への明示的なキャストで比較を実行します。

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::DATE;

Copy

+---------------------------------+
| DATE1                           |
|---------------------------------|
| Fri, 05 Apr 2013 00:00:00 -0700 |
| Sat, 06 Apr 2013 00:00:00 -0700 |
| Sat, 01 Jan 2000 00:00:00 -0800 |
+---------------------------------+

変換関数の詳細については、変換関数の日付と時刻の形式をご参照ください。

日付演算の日付文字列への適用¶

文字列で表現された日付に5日を追加します。

SELECT DATEADD('day',
               5,
               TO_TIMESTAMP('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'))
  AS add_five_days;

Copy

+-------------------------+
| ADD_FIVE_DAYS           |
|-------------------------|
| 2024-01-17 00:00:00.000 |
+-------------------------+

DATEDIFF 関数を使用して、現在の日付と文字列で表現された日付の日数の差を計算します。

TO_TIMESTAMP 関数を使って日数の差を計算します。

SELECT DATEDIFF('day',
                TO_TIMESTAMP ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_timestamp_difference;

Copy

+-------------------------+
| TO_TIMESTAMP_DIFFERENCE |
|-------------------------|
|                     229 |
+-------------------------+

TO_DATE 関数を使って日数の差を計算します。

SELECT DATEDIFF('day',
                TO_DATE ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_date_difference;

Copy

+--------------------+
| TO_DATE_DIFFERENCE |
|--------------------|
|                229 |
+--------------------+

指定した日付に1日を追加します。

SELECT TO_DATE('2024-01-15') + 1 AS date_plus_one;

Copy

+---------------+
| DATE_PLUS_ONE |
|---------------|
| 2024-01-16    |
+---------------+

現在の日付から9日引く（例：2024年8月28日）：

SELECT CURRENT_DATE() - 9 AS date_minus_nine;

Copy

+-----------------+
| DATE_MINUS_NINE |
|-----------------|
| 2024-08-19      |
+-----------------+

日付または時刻の差の計算¶

現在の日付と3年後の日付の差を計算します。

SELECT DATEDIFF(year, CURRENT_DATE(),
         DATEADD(year, 3, CURRENT_DATE()));

Copy

現在の日付と3か月後の日付の差を計算します。

SELECT DATEDIFF(month, CURRENT_DATE(),
         DATEADD(month, 3, CURRENT_DATE()));

Copy

現在の日付と3日後の日付の差を計算します。

SELECT DATEDIFF(day, CURRENT_DATE(),
         DATEADD(day, 3, CURRENT_DATE()));

Copy

現在の時刻と3時間後の時刻との差を計算します。

SELECT DATEDIFF(hour, CURRENT_TIMESTAMP(),
         DATEADD(hour, 3, CURRENT_TIMESTAMP()));

Copy

現在の時刻と3分後の時刻との差を計算します。

SELECT DATEDIFF(minute, CURRENT_TIMESTAMP(),
         DATEADD(minute, 3, CURRENT_TIMESTAMP()));

Copy

現在の時刻と3秒後の時刻との差を計算します。

SELECT DATEDIFF(second, CURRENT_TIMESTAMP(),
         DATEADD(second, 3, CURRENT_TIMESTAMP()));

Copy

年間カレンダービューの作成¶

CREATE OR REPLACE VIEW calendar_2016 AS
  SELECT n,
         theDate,
         DECODE (EXTRACT('dayofweek',theDate),
           1 , 'Monday',
           2 , 'Tuesday',
           3 , 'Wednesday',
           4 , 'Thursday',
           5 , 'Friday',
           6 , 'Saturday',
           0 , 'Sunday') theDayOfTheWeek,
         DECODE (EXTRACT(month FROM theDate),
           1 , 'January',
           2 , 'February',
           3 , 'March',
           4 , 'April',
           5 , 'May',
           6 , 'June',
           7 , 'July',
           8 , 'August',
           9 , 'september',
           10, 'October',
           11, 'November',
           12, 'December') theMonth,
         EXTRACT(year FROM theDate) theYear
  FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY seq4()) AS n,
            DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq4())-1, TO_DATE('2016-01-01')) AS theDate
      FROM table(generator(rowCount => 365)))
  ORDER BY n ASC;

SELECT * from CALENDAR_2016;

Copy

+-----+------------+-----------------+-----------+---------+
|   N | THEDATE    | THEDAYOFTHEWEEK | THEMONTH  | THEYEAR |
|-----+------------+-----------------+-----------+---------|
|   1 | 2016-01-01 | Friday          | January   |    2016 |
|   2 | 2016-01-02 | Saturday        | January   |    2016 |
  ...
| 364 | 2016-12-29 | Thursday        | December  |    2016 |
| 365 | 2016-12-30 | Friday          | December  |    2016 |
+-----+------------+-----------------+-----------+---------+