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¶

Dieser Abschnitt enthält Beispiele für das Laden von Datums- und Zeitstempelwerten sowie Hinweise zu Zeitzonen beim Laden dieser Werte.

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 America/Chicago Zeit festgelegt. Wenn für einige eingehende Zeitstempel keine Zeitzone angegeben ist, lädt Snowflake die Zeichenfolgen mit der Annahme, dass die Zeitstempel die lokale in der Zeitzone darstellen, die für den Parameter TIMEZONE eingestellt ist.

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

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 America/Chicago Zeit festgelegt. Wenn für einige eingehende Zeitstempel eine andere Zeitzone angegeben ist, lädt Snowflake die Zeichenfolge in America/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 ('2024-04-30 19:00:00.000 -0800');

SELECT * FROM time;

Copy

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

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 ('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 |
+---------------------------+

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

Dieses Beispiel fügt Werte in eine DATE-Spalte ein.

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

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

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

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;

Copy

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

Wenn Sie DATE-Werte abrufen, können Sie diese als TIMESTAMP-Werte formatieren:

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

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.

Sie können den aktuellen Wochentag als Zeichenfolge abrufen, indem Sie die Funktion TO_VARCHAR oder DECODE verwenden.

Führen Sie eine Abfrage aus, die die englische Kurzbezeichnung (z. B. „Sun“, „Mon“ usw.) für das aktuelle Datum zurückgibt:

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

Copy

Führen Sie eine Abfrage aus, die die explizit bereitgestellten Wochentagsnamen für das aktuelle Datum liefert:

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

Abrufen von Teilen eines Datums oder einer Uhrzeit¶

Mit der Funktion DATE_PART können Sie verschiedene Datums- und Zeitangaben für das aktuelle Datum und die aktuelle Uhrzeit abrufen.

Abfrage nach dem aktuellen Tag des Monats:

SELECT DATE_PART(day, CURRENT_TIMESTAMP());

Copy

Abfrage für das laufende Jahr:

SELECT DATE_PART(year, CURRENT_TIMESTAMP());

Copy

Abfrage für den aktuellen Monat:

SELECT DATE_PART(month, CURRENT_TIMESTAMP());

Copy

Abfrage für die aktuelle Stunde:

SELECT DATE_PART(hour, CURRENT_TIMESTAMP());

Copy

Abfrage für die aktuelle Minute:

SELECT DATE_PART(minute, CURRENT_TIMESTAMP());

Copy

Abfrage für die aktuelle Sekunde:

SELECT DATE_PART(second, CURRENT_TIMESTAMP());

Copy

Sie können auch die Funktion EXTRACT verwenden, um verschiedene Datums- und Zeitangaben für das aktuelle Datum und die aktuelle Uhrzeit zu erhalten.

Abfrage nach dem aktuellen Tag des Monats:

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

Copy

Abfrage für das laufende Jahr:

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

Copy

Abfrage für den aktuellen Monat:

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

Copy

Abfrage für die aktuelle Stunde:

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

Copy

Abfrage für die aktuelle Minute:

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

Copy

Abfrage für die aktuelle Sekunde:

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

Copy

Diese Abfrage gibt eine tabellarische Ausgabe mit verschiedenen Datums- und Zeitangaben für das aktuelle Datum und die aktuelle Uhrzeit zurück:

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

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

Als Alternative ruft im folgenden Beispiel DATE_TRUNC Anfang des aktuellen Monats ab, fügt einen Monat hinzu, um den Anfang des nächsten Monats abzurufen, und zieht dann einen 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

Ermittelt den kurzen englischen Namen (z. B. „Jan“, „Dec“ usw.) für den aktuellen Monat:

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

Copy

Ermittelt den aktuellen Monatsnamen anhand der explizit angegebenen Monatsnamen:

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 und so weiter abzurufen, ersetzen Sie 2, 3 usw. 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());

Copy

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

Inkrementieren von Datums- und Zeitwerten¶

Verwenden Sie die Funktion DATEADD, um das Datums- und die Uhrzeitwerte zu erhöhen.

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:

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

Die folgende Abfrage führt einen Vergleich ohne explizite Umwandung durch:

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

Copy

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

Die folgende Abfrage führt einen Vergleich mit expliziter Umwandlung zu DATE durch:

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

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

Sie können die Differenz in Tagen zwischen dem aktuellen Datum und dem in einer Zeichenfolge ausgedrückten Datum mit der Funktion DATEDIFF berechnen.

Berechnen der Differenz in Tagen mit der Funktion 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 |
+-------------------------+

Berechnen der Differenz in Tagen mit der Funktion 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 |
+--------------------+

Einen Tag zu einem bestimmten Datum hinzufügen:

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

Copy

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

Ziehen Sie neun Tage vom aktuellen Datum ab (zum Beispiel 28. August 2024):

SELECT CURRENT_DATE() - 9 AS date_minus_nine;

Copy

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

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;

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