Verwenden von Datums- und Zeitwerten

Datums- und Zeitberechnungen gehören zu den am häufigsten verwendeten und kritischsten Berechnungen in der Analytik und im Data Mining. Unter diesem Thema finden Sie praktische Beispiele für gängige Abfragen und Berechnungen von Datum und Uhrzeit.

Unter diesem Thema:

Laden von Datumsangaben und Zeitstempeln

Laden von Zeitstempeln ohne Angabe der Zeitzone

Im folgenden Beispiel wird der Parameter TIMESTAMP_TYPE_MAPPING auf TIMESTAMP_LTZ (lokale Zeitzone) gesetzt. Der Parameter TIMEZONE ist auf die Amerika/Chicago-Zeit eingestellt. Wenn für einen Satz eingehender Zeitstempel keine Zeitzone angegeben ist, lädt Snowflake die Zeichenfolgen mit der Annahme, dass die Zeitstempel die lokale Zeit in der eingestellten Zeitzone darstellen.

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

Laden von Zeitstempeln mit Angabe der Zeitzone

Im folgenden Beispiel wird der Parameter TIMESTAMP_TYPE_MAPPING auf TIMESTAMP_LTZ (lokale Zeitzone) gesetzt. Der Parameter TIMEZONE ist auf die Amerika/Chicago-Zeit eingestellt. Angenommen, für einen Satz von eingehenden Zeitstempeln wurde eine andere Zeitzone angegeben. Snowflake lädt die Zeichenfolge in Amerika/Chicago-Zeit.

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

Konvertieren von Zeitstempeln in alternative Zeitzonen

Im folgenden Beispiel wird ein Satz von Zeitstempelwerten ohne Zeitzonendaten gespeichert. Die Zeitstempel werden in UTC-Zeit geladen und in andere Zeitzonen umgewandelt:

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

Einfügen von gültigen Datumszeichenfolgen in Datumsspalten einer Tabelle

Dieses Beispiel zeigt, wie Sie einen Wert in eine DATE-Spalte einfügen.

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

Die Funktion TO_DATE akzeptiert TIMESTAMP-Werte und sogar Zeichenfolgen im TIMESTAMP-Format, verwirft jedoch die Zeitinformationen (Stunden, Minuten usw.).

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

Wenn Sie einen DATE-Wert einfügen, der nur mit einer Uhrzeit definiert wurde, lautet das Standarddatum 1. Januar 1970.

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

Wenn Sie DATE-Informationen abrufen, können Sie diese auf Wunsch als TIMESTAMP formatieren:

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

Abrufen des aktuellen Datums und der aktuellen Uhrzeit

Liefert das aktuelle Datum als DATE-Wert:

SELECT current_date();
Copy

Liefert das aktuelle Datum und die aktuelle Uhrzeit als TIMESTAMP-Wert:

SELECT current_timestamp();
Copy

Abrufen von Datumsangaben und Wochentagen

Mit der Funktion EXTRACT erhalten Sie den aktuellen Wochentag als Zahl:

SELECT EXTRACT('dayofweek',current_date());
Copy

Bemerkung

  • Der Teil dayofweek_iso folgt dem ISO 8601-Standard für Datenelemente und Austauschformate. Die Funktion gibt den Wochentag als ganzzahligen Wert im Bereich von 1-7 zurück, wobei 1 für Montag steht.

  • Aus Kompatibilitätsgründen mit einigen anderen Systemen folgt der Teil dayofweek dem UNIX-Standard. Die Funktion gibt den Wochentag als ganzzahligen Wert im Bereich von 0–6 zurück, wobei 0 für Sonntag steht.

Mit der Funktion TO_VARCHAR oder DECODE erhalten Sie den aktuellen Wochentag als Zeichenfolge:

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

Abrufen von Teilen eines Datums oder einer Uhrzeit

Mit der Funktion DATE_PART erhalten Sie verschiedene Teile des aktuellen Datums oder der aktuellen Uhrzeit:

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

Alternativ können Sie auch die Funktion EXTRACT verwenden:

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

Alternative tabellarische Ausgabe:

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

Berechnen von Datumsangaben und Uhrzeiten des Geschäftskalenders

Mit der Funktion DATE_TRUNC erhalten Sie den ersten Tag des Monats als DATE-Wert. Ermitteln Sie beispielsweise den ersten Tag des aktuellen Monats:

SELECT DATE_TRUNC('month', current_date());
Copy

Mit den Funktionen DATEADD und DATE_TRUNC erhalten Sie den letzten Tag des aktuellen Monats als DATE-Wert:

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

Alternative Option: Im folgenden Beispiel ruft DATE_TRUNC den Anfang des aktuellen Monats ab, fügt einen Monat hinzu, um den Anfang des nächsten Monats abzurufen, und zieht dann 1 Tag ab, um den letzten Tag des aktuellen Monats zu bestimmen.

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

So erhalten Sie den letzten Tag des Vormonats als DATE-Wert:

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

So erhalten Sie den aktuellen Monat des Jahres nach Name:

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

So erhalten Sie das Datum für den Montag der aktuellen Woche:

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

So erhalten Sie das Datum für den Freitag der aktuellen Woche:

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

Mit der Funktion DATE_PART erhalten Sie das Datum für den ersten Montag des aktuellen Monats:

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

Bemerkung

In der obigen Abfrage wird der Wert 1 in 7 + 1 als Montag interpretiert. Um das Datum für den ersten Dienstag, Mittwoch usw. abzurufen, ersetzen Sie 2, 3 und so weiter jeweils durch 7 für Sunday.

So erhalten Sie den ersten Tag des aktuellen Jahres als DATE-Wert:

SELECT DATE_TRUNC('year', current_date());
Copy

So erhalten Sie den letzten Tag des aktuellen Jahres als DATE-Wert:

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

So erhalten Sie den letzten Tag des Vorjahres als DATE-Wert:

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

So erhalten Sie den ersten Tag des aktuellen Quartals als DATE-Wert:

SELECT DATE_TRUNC('quarter',current_date());
Copy

So erhalten Sie den letzten Tag des aktuellen Quartals als DATE-Wert:

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

So erhalten Sie das Datum und den Zeitstempel für Mitternacht des aktuellen Tags:

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

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

Inkrementieren von Datums- und Zeitwerten

Fügen Sie dem aktuellen Datum zwei Jahre hinzu:

SELECT DATEADD(year, 2, current_date());
Copy

Fügen Sie dem aktuellen Datum zwei Tage hinzu:

SELECT DATEADD(day,2,current_date());
Copy

Fügen Sie dem aktuellen Datum und der aktuellen Uhrzeit zwei Stunden hinzu:

SELECT DATEADD(hour,2,current_timestamp());
Copy

Fügen Sie dem aktuellen Datum und der aktuellen Uhrzeit zwei Minuten hinzu:

SELECT DATEADD(minute,2,current_timestamp());
Copy

Fügen Sie dem aktuellen Datum und der aktuellen Uhrzeit zwei Sekunden hinzu:

SELECT DATEADD(second,2,current_timestamp());
Copy

Konvertieren gültiger Zeichenfolgen in Datum, Uhrzeit oder Zeitstempel

In den meisten Anwendungsfällen verarbeitet Snowflake Datums- und Zeitstempelwerte, die als Zeichenfolgen formatiert sind, korrekt. In bestimmten Fällen, wie z. B. bei zeichenfolgenbasierten Vergleichen oder wenn ein Ergebnis von einem anderen Zeitstempelformat abhängt, als in den Sitzungsparametern eingestellt, empfehlen wir, Werte explizit in das gewünschte Format zu konvertieren, um unerwartete Ergebnisse zu vermeiden.

So führt beispielsweise der Vergleich von Zeichenfolgenwerten ohne explizite Umwandlung zu zeichenfolgenbasierten Ergebnissen:

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

Weitere Informationen zu den Konvertierungsfunktionen finden Sie unter Datums- und Uhrzeitformate in Konvertierungsfunktionen.

Anwenden von Datumsarithmetik auf gültige Datumszeichenfolgen

Fügen Sie fünf Tage zu dem Datum hinzu, das in einer Zeichenfolge ausgedrückt wird:

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

Berechnen Sie mit der Funktion DATEDIFF die Differenz in Tagen zwischen dem aktuellen Datum und dem in einer Zeichenfolge ausgedrückten Datum:

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

Einen Tag zu einem bestimmten Datum hinzufügen:

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

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

9 Tage vom aktuellen Datum abziehen (z. B. vom 21. Januar 2019):

SELECT CURRENT_DATE() - 9;

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

Berechnen von Differenzen zwischen Datumsangaben oder Uhrzeiten

Berechnen Sie die Differenz zwischen dem aktuellen Datum und dem Datum in drei Jahren:

SELECT DATEDIFF(year, current_date(),
  DATEADD(year, 3, current_date() ) );
Copy

Berechnen Sie die Differenz zwischen dem aktuellen Datum und dem Datum in drei Monaten:

SELECT DATEDIFF(month, current_date(),
  DATEADD(month, 3, current_date()) );
Copy

Berechnen Sie die Differenz zwischen dem aktuellen Datum und dem Datum in drei Tagen:

SELECT DATEDIFF(day, current_date(),
  DATEADD(day, 3, current_date()) );
Copy

Berechnen Sie die Differenz zwischen der aktuellen Zeit und der Zeit in drei Stunden:

SELECT DATEDIFF(hour, current_timestamp(),
  DATEADD(hour, 3, current_timestamp()) );
Copy

Berechnen Sie die Differenz zwischen der aktuellen Zeit und der Zeit in drei Minuten:

SELECT DATEDIFF(minute, current_timestamp(),
  DATEADD(minute, 3, current_timestamp()) );
Copy

Berechnen Sie die Differenz zwischen der aktuellen Zeit und der Zeit in drei Sekunden:

SELECT DATEDIFF(second, current_timestamp(),
  DATEADD(second, 3, current_timestamp()) );
Copy

Erstellen von Jahreskalenderansichten

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
Sprache: Deutsch