Datentypen für Datum und Uhrzeit

Unter diesem Thema werden die in Snowflake unterstützten Datentypen zum Verwalten von Datumsangaben, Uhrzeiten und Zeitstempeln (Kombination aus Datum und Uhrzeit) beschrieben. Außerdem werden die unterstützten Formate für Zeichenfolgenkonstanten beschrieben, die zum Bearbeiten von Datumsangaben, Uhrzeiten und Zeitstempeln verwendet werden.

Unter diesem Thema:

Datentypen

DATE

Snowflake unterstützt nur einen DATE-Datentyp zum Speichern von Datumsangaben (ohne Zeitelemente). DATE akzeptiert Datumsangaben in den gebräuchlichsten Formen wie YYYY-MM-DD, DD-MON-YYYY usw. Alle akzeptierten Zeitstempel sind auch gültige Eingaben für Datumsangaben, allerdings wird die TIME-Information abgeschnitten.

DATETIME

DATETIME ist ein Alias für TIMESTAMP_NTZ.

TIME

Snowflake unterstützt einen einzigen TIME-Datentyp zum Speichern von Zeiten im Format HH:MI:SS. TIME unterstützt einen optionalen Genauigkeitsparameter für Sekundenbruchteile, z. B. TIME(3). Die Zeitgenauigkeit kann zwischen 0 (Sekunden) und 9 (Nanosekunden) liegen. Die Standardgenauigkeit ist 9.

Alle TIME-Werte müssen zwischen 00:00:00 und 23:59:59.999999999 liegen. TIME speichert intern die lokale Zeit vor Ort, d. h. alle Operationen für TIME-Werte werden ohne jegliche Berücksichtigung von Zeitzonen durchgeführt.

TIMESTAMP

TIMESTAMP in Snowflake ist ein benutzerdefinierter Alias, der einer der TIMESTAMP_*-Varianten zugeordnet ist. Bei allen Operationen, in denen TIMESTAMP verwendet wird, wird automatisch die zugeordnete TIMESTAMP\_\*-Variante verwendet. Der Datentyp TIMESTAMP wird niemals in Tabellen gespeichert.

Die mit TIMESTAMP verknüpfte TIMESTAMP_*-Variante wird durch den Sitzungsparameter TIMESTAMP_TYPE_MAPPING angegeben. Die Voreinstellung ist TIMESTAMP_NTZ.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflake unterstützt drei Zeitstempelvarianten. Alle Zeitstempelvarianten und der TIMESTAMP-Alias unterstützen einen optionalen Genauigkeitsparameter für Sekundenbruchteile, z. B. TIMESTAMP(3). Die Genauigkeit eines Zeitstempels kann zwischen 0 (Sekunden) und 9 (Nanosekunden) liegen. Die Standardgenauigkeit lautet 9.

TIMESTAMP_LTZ

TIMESTAMP_LTZ speichert UTC-Zeit intern mit der angegebenen Genauigkeit. Alle Operationen werden jedoch in der Zeitzone der aktuellen Sitzung ausgeführt, gesteuert vom Sitzungsparameter TIMEZONE.

Aliasse für TIMESTAMP_LTZ:

  • TIMESTAMPLTZ

  • TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP_NTZ

TIMESTAMP_NTZ speichert intern die „Wanduhr“-Zeit mit einer bestimmten Genauigkeit. Alle Operationen werden ohne Berücksichtigung von Zeitzonen durchgeführt.

Wenn das Ausgabeformat eine Zeitzone enthält, wird der UTC-Indikator (Z) angezeigt.

TIMESTAMP_NTZ ist die Standardeinstellung für TIMESTAMP.

Aliasse für TIMESTAMP_NTZ:

  • TIMESTAMPNTZ

  • TIMESTAMP WITHOUT TIME ZONE

TIMESTAMP_TZ

TIMESTAMP_TZ speichert die UTC-Zeit intern zusammen mit einem zugehörigen Zeitzonenoffset. Wenn keine Zeitzone angegeben ist, wird der Zeitzonenoffset der Sitzung verwendet. Alle Operationen werden mit dem für jeweiligen Datensatz spezifischen Zeitzonenoffset durchgeführt.

Aliasse für TIMESTAMP_TZ:

  • TIMESTAMPTZ

  • TIMESTAMP WITH TIME ZONE

Achtung

Aktuell speichert TIMESTAMP_TZ zum Zeitpunkt der Erstellung für einen bestimmten Wert nur den Offset einer gegebenen Zeitzone, nicht die Zeitzone selbst. Dies ist besonders wichtig, wenn Sie die Sommerzeit verarbeiten müssen, die von UTC nicht verwendet wird.

Wenn der Parameter TIMEZONE beispielsweise auf "America/Los_Angeles" gesetzt ist, wird beim Konvertieren eines Werts in TIMESTAMP_TZ im Januar eines gegebenen Jahres der Zeitzonenoffset -0800 gespeichert. Wenn der Wert später um 6 Monate erhöht wird, wird der Offset -0800 beibehalten, obwohl der Offset für Los Angeles im Juli -0700 lautet. Das liegt daran, dass nach Erstellung des Werts die Information zur aktuellen Zeitzone ("America/Los_Angeles") nicht mehr verfügbar ist. Das folgende Codebeispiel veranschaulicht dieses Verhalten:

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

Beispiele für Zeitstempel

Erstellen einer Tabelle mit verschiedenen Zeitstempeln:

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

Verwenden von TIMESTAMP_LTZ mit verschiedenen Zeitzonen:

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

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

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

Unterstützter Kalender

Snowflake verwendet für alle Datumsangaben und Zeitstempel den gregorianischen Kalender. Der gregorianische Kalender beginnt im Jahr 1582, berücksichtigt jedoch frühere Jahre. Dies ist wichtig, da Snowflake keine Datumsangaben vor 1582 (oder Berechnungen mit Datumsangaben vor 1582) anpasst, um mit dem julianischen Kalender übereinzustimmen.

Datums- und Uhrzeitformate

Alle diese Datentypen akzeptieren die meisten vernünftigen, eindeutigen Datums-, Zeit- oder Datums- und Zeitformate. Manuell festgelegte Datums- und Uhrzeitformate können folgende Elemente beinhalten (ohne Berücksichtigung von Groß- und Kleinschreibung):

Format

Beschreibung

YYYY

Vierstellige Jahresangabe.

YY

Zweistellige Jahresangabe, gesteuert durch den Sitzungsparameter TWO_DIGIT_CENTURY_START, z. B. wenn dieser auf 1980 gesetzt ist, die Werte 79 und 80, die als 2079 bzw. 1980 geparst werden.

MM

Zweistellige Monatsangabe (01=Januar usw.).

MON

Abgekürzter Monatsname.

DD

Zweistellige Tagesangabe des Monats (01 bis 31).

DY

Abgekürzter Wochentag.

HH24

Zwei Ziffern für die Stunde (00 bis 23); am/pm nicht erlaubt.

HH12

Zwei Ziffern für die Stunde (01 bis 12); „am“/„pm“ NICHT erlaubt.

AM, PM

Ante Meridiem (am) / Post Meridiem (pm); zur Verwendung mit HH12.

MI

Zwei Ziffern für die Minute (00 bis 59).

SS

Zwei Ziffern für die Sekunde (00 bis 59).

FF

Sekundenbruchteile mit einer Genauigkeit von 0 (Sekunden) bis 9 (Nanosekunden), z. B. FF, FF0, FF3, FF9. Die Angabe von FF entspricht FF9 (Mikrosekunden).

TZH:TZM, TZHTZM, TZH

Zeitzonenstunde und -minute, Offset von UTC. Kann +/- als Vorzeichen erhalten.

Bemerkung

Bei Einsatz eines reinen Datumsformats wird davon ausgegangen, dass die zugehörige Uhrzeit Mitternacht an diesem Tag ist.

Beispiele für die Verwendung von Datums- und Uhrzeitformaten

Im folgenden Beispiel wird „FF“ verwendet, um anzugeben, dass die Ausgabe im Feld „Sekundenbruchteil“ 9 Ziffern haben soll:

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

Datums- und Uhrzeitkonstanten

Konstanten (auch als Literale bekannt) beziehen sich auf feste Datenwerte. Snowflake unterstützt die Angabe von festen Datums-, Zeit- oder Zeitstempelwerten unter Verwendung von Zeichenfolgenkonstanten. Zeichenfolgenkonstanten müssen immer zwischen Trennzeichen eingeschlossen sein. Snowflake unterstützt die Nutzung einfacher Anführungszeichen zum Trennen von Zeichenfolgenkonstanten.

Beispiel:

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

Die Zeichenfolge wird als DATE-, TIME- oder TIMESTAMP-Wert analysiert, basierend auf dem Eingabeformat für den Datentyp, wie durch die folgenden Parameter festgelegt:

DATE

DATE_INPUT_FORMAT

TIME

TIME_INPUT_FORMAT

TIMESTAMP

TIMESTAMP_INPUT_FORMAT

Fügen Sie beispielsweise ein bestimmtes Datum in eine Spalte einer Tabelle ein:

CREATE TABLE t1 (d1 DATE);

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

Intervallkonstanten

Sie können Intervallkonstanten verwenden, um einen Zeitraum zu bzw. von einem Datum, einer Uhrzeit oder einem Zeitstempel zu addieren oder abzuziehen. Intervallkonstanten werden mit dem Schlüsselwort INTERVAL implementiert, das die folgende Syntax aufweist:

{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'

Wie bei allen Zeichenfolgenkonstanten erfordert Snowflake einfache Anführungszeichen, um Intervallkonstanten zu trennen.

Das Schlüsselwort INTERVAL unterstützt eine oder mehrere Ganzzahlen und optional eine oder mehrere Datums- oder Uhrzeitkomponenten. Beispiel:

  • INTERVAL '1 YEAR' steht für 1 Jahr.

  • INTERVAL '4 years, 5 months, 3 hours' steht für 4 Jahre, 5 Monate und 3 Stunden.

Wenn keine Datums- oder Zeitkomponente angegeben ist, stellt das Intervall Sekunden dar (so entspricht INTERVAL '2' z. B. INTERVAL '2 seconds'). Beachten Sie, dass sich dies von der Standardzeiteinheit zur Durchführung der Datumsberechnung unterscheidet. Weitere Details dazu finden Sie unter Einfache Arithmetik für Datumsangaben (unter diesem Thema).

Die Liste der unterstützten Datums- und Uhrzeitkomponenten finden Sie unter Unterstützte Datums- und Uhrzeitkomponenten bei Intervallen (unter diesem Thema).

Bemerkung

  • Die Reihenfolge der Intervallinkremente ist wichtig. Die Inkremente werden in der angegebenen Reihenfolge addiert oder subtrahiert. Beispiel:

    • INTERVAL '1 year, 1 day' addiert/subtrahiert zuerst ein Jahr und dann einen Tag.

    • INTERVAL '1 day, 1 year' addiert/subtrahiert zuerst einen Tag und dann ein Jahr.

    Dies kann sich auf Berechnungen auswirken, die durch Kalenderereignisse wie Schaltjahre beeinflusst werden:

    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 ist kein Datentyp (d. h. Sie können keine Tabellenspalte mit dem Datentyp INTERVAL definieren). Intervalle können nur für Datums-, Uhrzeit- und Zeitstempelberechnungen verwendet werden.

Unterstützte Datums- und Uhrzeitkomponenten bei Intervallen

Das Schlüsselwort INTERVAL unterstützt die folgenden Datums- und Uhrzeitkomponenten als Argumente (ohne Berücksichtigung der Groß- und Kleinschreibung):

Datums- oder Zeitkomponente

Abkürzungen/Varianten

year

y, yy, yyy, yyyy, yr, years, yrs

quarter

q, qtr, qtrs, quarters

month

mm, mon, mons, months

week

w, wk, weekofyear, woy, wy, weeks

day

d, dd, days, dayofmonth

hour

h, hh, hr, hours, hrs

minute

m, mi, min, minutes, mins

second

s, sec, seconds, secs

millisecond

ms, msec, milliseconds

microsecond

us, usec, microseconds

nanosecond

ns, nsec, nanosec, nsecond, nanoseconds, nanosecs, nseconds

Beispiele für Intervalle

Fügt zu einem bestimmten Datum ein Intervall von einem Jahr hinzu:

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

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-04-15                                |
+-------------------------------------------+

Fügen Sie zu einer bestimmten Zeit ein Intervall von 3 Stunden und 18 Minuten hinzu:

select to_time('04:15:29') + INTERVAL '3 hours, 18 minutes';

+------------------------------------------------------+
| TO_TIME('04:15:29') + INTERVAL '3 HOURS, 18 MINUTES' |
|------------------------------------------------------|
| 07:33:29                                             |
+------------------------------------------------------+

Fügt der Ausgabe der Funktion CURRENT_TIMESTAMP ein komplexes Intervall hinzu:

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

Fügt ein komplexes Intervall mit verkürzter Notation für Datums-/Zeitkomponenten zu einem bestimmten Datum hinzu:

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

Fragt eine Tabelle mit Mitarbeiterinformationen ab und gibt die Namen von Mitarbeitern zurück, die in den letzten 2 Jahren und 3 Monaten eingestellt wurden:

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

Filtert eine Zeitstempelspalte namens ts aus einer Tabelle namens tl und addiert 4 Sekunden zu jedem zurückgegebenen Wert:

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

Einfache Arithmetik für Datumsangaben

Zusätzlich zur Verwendung von Intervallkonstanten zum Hinzufügen und Subtrahieren von Datumsangaben, Uhrzeiten und Zeitstempeln unterstützt Snowflake auch das grundlegende Addieren und Subtrahieren von Tagen bei DATE-Werten in Form von { + | - } <Ganzzahl>, wobei <Ganzzahl> die Zahl der Tage angibt, die addiert/subtrahiert werden sollen.

Bemerkung

TIME- und TIMESTAMP-Werte unterstützen noch keine einfache Arithmetik.

Beispiele für Datumsberechnungen

Fügt 1 Tag zu einem bestimmten Datum hinzu:

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

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

Subtrahiert 4 Tage von einem bestimmten Datum:

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

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

Fragt eine Tabelle namens employees ab und gibt die Namen von Personen zurück, die das Unternehmen verlassen haben, aber mehr als 365 Tage im Unternehmen beschäftigt waren:

select name from employees where end_date > start_date + 365;