日付と時刻の値の操作

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

このトピックの内容:

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

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

次の例では、 TIMESTAMP_TYPE_MAPPING パラメーターは TIMESTAMP_LTZ （ローカルタイムゾーン）に設定されています。 TIMEZONE パラメーターはアメリカ/シカゴ時間に設定されています。一連の着信タイムスタンプにタイムゾーンが指定されていない場合、Snowflakeは、タイムスタンプが設定されたタイムゾーンの現地時間を表すと仮定して、これらの文字列をロードします。

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 ('2016-05-01 00:00:00.000');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sun, 01 May 2016 00:00:00 -0500 |
+---------------------------------+
Copy

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

次の例では、 TIMESTAMP_TYPE_MAPPING パラメーターは TIMESTAMP_LTZ （ローカルタイムゾーン）に設定されています。 TIMEZONE パラメーターはアメリカ/シカゴ時間に設定されています。一連の着信タイムスタンプに異なるタイムゾーンが指定されているとします。Snowflake はアメリカ/シカゴ時間に文字列をロードします。

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 ('2016-04-30 19:00:00.000 -0800');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sat, 30 Apr 2016 22:00:00 -0500 |
+---------------------------------+
Copy

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

次の例では、タイムゾーンデータがない一連のタイムスタンプ値が保存されます。タイムスタンプは 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 ('2016-05-01 00:00:00.000');

SELECT * FROM utctime;

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

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

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

この例は、値を DATE 列に挿入する方法を示しています。

CREATE TABLE my_table(id INTEGER, date1 DATE);
-- July 23, 2016.
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2016.07.23', 'YYYY.MM.DD'));
-- NULL.
INSERT INTO my_table(id) VALUES (2);
Copy
SELECT id, date1 
    FROM my_table 
    ORDER BY id;
+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2016-07-23 |
|  2 | NULL       |
+----+------------+
Copy

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

INSERT INTO my_table(id, date1) VALUES
    (3, TO_DATE('2020.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
    (4, TO_TIMESTAMP('2020.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));
Copy
SELECT id, date1 
    FROM my_table 
    WHERE id >= 3;
+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2020-02-20 |
|  4 | 2020-02-24 |
+----+------------+
Copy

時刻のみで定義された 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;
+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+
Copy

DATE 情報を取得するときに、必要に応じて TIMESTAMP としてフォーマットできます。

SELECT id, TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
    FROM my_table 
    ORDER BY id;
+----+----------------------+
| ID | DATE1                |
|----+----------------------|
|  1 | 23-Jul-2016 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2020 00:00:00 |
|  4 | 24-Feb-2020 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+
Copy

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

現在の日付を 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 関数を使用して、現在の曜日を文字列として取得します。

-- Output short English names, e.g. "Sun", "Mon" etc.
SELECT TO_VARCHAR(current_date(), 'DY');

-- Output arbitrary, explicitly-provided weekday names:
SELECT DECODE(EXTRACT ('dayofweek_iso',current_date()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday');
Copy

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

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

-- Current day of the month
SELECT DATE_PART(day, current_timestamp());

-- Current year
SELECT DATE_PART(year, current_timestamp());

-- Current month
SELECT DATE_PART(month, current_timestamp());

-- Current hour
SELECT DATE_PART(hour, current_timestamp());

-- Current minute
SELECT DATE_PART(minute, current_timestamp());

-- Current second
SELECT DATE_PART(second, current_timestamp());
Copy

EXTRACT 関数を使用する代替オプション：

-- Current day of the month
SELECT EXTRACT('day', current_timestamp());

-- Current year
SELECT EXTRACT('year', current_timestamp());

-- Current month
SELECT EXTRACT('month', current_timestamp());

-- Current hour
SELECT EXTRACT('hour', current_timestamp());

-- Current minute
SELECT EXTRACT('minute', current_timestamp());

-- Current second
SELECT EXTRACT('second', current_timestamp());
Copy

代替の表形式出力：

SELECT day(current_timestamp() ) ,
  hour( current_timestamp() ) ,
  second(current_timestamp()) ,
  minute(current_timestamp()) ,
  month(current_timestamp());

  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+
  | DAY(CURRENT_TIMESTAMP() ) | HOUR( CURRENT_TIMESTAMP() ) | SECOND(CURRENT_TIMESTAMP()) | MINUTE(CURRENT_TIMESTAMP()) | MONTH(CURRENT_TIMESTAMP()) |
  |---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------|
  |                         7 |                           6 |                          43 |                          44 |                          9 |
  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+
Copy

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

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

名前で年の現在の月を取得します。

-- Output short English names, e.g. "Jan", "Dec", etc.
SELECT TO_VARCHAR(current_date(), 'Mon');

-- Output arbitrary, explicitly-provided month names
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 のようにして、 Sunday までを 7 までで置き換えます。

現在の年の初日を 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());

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

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

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

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

-- Note the column data type is string
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');

-- Comparison without explicit casting
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

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

-- Comparison with explicit casting to date
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::date;

+---------------------------------+
| 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 |
+---------------------------------+
Copy

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

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

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

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

+--------------------------------------------------------------------------------+
| DATEADD('DAY',5,TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') ) |
|--------------------------------------------------------------------------------|
| Thu, 17 Jan 2016 00:00:00 -0800                                                |
+--------------------------------------------------------------------------------+
Copy

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

-- Using the TO_TIMESTAMP function:
SELECT DATEDIFF('day', TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') , current_date() );

+-------------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') , CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------------|
|                                                                                            240  |
+-------------------------------------------------------------------------------------------------+

-- Using the TO_DATE function:
SELECT DATEDIFF('day', TO_DATE ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss'), current_date() );

+-------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_DATE ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS'), CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------|
|                                                                                      240  |
+-------------------------------------------------------------------------------------------+
Copy

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

SELECT TO_DATE('2019-01-15') + 1;

+---------------------------+
| TO_DATE('2019-01-15') + 1 |
|---------------------------|
| 2019-01-16                |
+---------------------------+
Copy

現在の日付から9日を引きます（例：2019年1月21日）

SELECT CURRENT_DATE() - 9;

+--------------------+
| CURRENT_DATE() - 9 |
|--------------------|
| 2019-01-12         |
+--------------------+
Copy

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

現在の日付と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;

+-----+------------+-----------------+-----------+---------+
|   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 |
+-----+------------+-----------------+-----------+---------+
Copy
言語: 日本語