Datentypen für Datum und Uhrzeit¶

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 TIMESTAMP-Werte sind auch gültige Eingaben für Datumswerte, allerdings wird die TIME-Information abgeschnitten.

Für DATE- und TIMESTAMP-Daten empfiehlt Snowflake die Verwendung von Jahreswerten zwischen 1582 und 9999. Snowflake akzeptiert einige Jahre außerhalb dieses Bereichs, aber Jahre vor 1582 sollten aufgrund von Einschränkungen im Gregorianischen Kalender vermieden werden.

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.

Für DATE- und TIMESTAMP-Daten empfiehlt Snowflake die Verwendung von Jahreswerten zwischen 1582 und 9999. Snowflake akzeptiert einige Jahre außerhalb dieses Bereichs, aber Jahre vor 1582 sollten aufgrund von Einschränkungen im Gregorianischen Kalender vermieden werden.

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 ist 9.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ¶

Snowflake unterstützt drei Zeitstempelvarianten.

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

TIMESTAMP_TZ-Werte werden anhand ihrer Zeiten in UTC verglichen. Der folgende Vergleich zwischen verschiedenen Zeiten in unterschiedlichen Zeitzonen gibt zum Beispiel TRUE zurück, da die beiden Werte äquivalente Zeiten in UTC haben.

select '2021-01-01 00:00:00 +0000'::timestamp_tz = '2021-01-01 01:00:00 +0100'::timestamp_tz;

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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 timestamp_demo_table(t  TIMESTAMP,
                                  t_tz TIMESTAMP_TZ,
                                  t_ntz TIMESTAMP_NTZ,
                                  t_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (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'
    );

Copy

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

Copy

SELECT t, t_tz, t_ntz, t_ltz 
    FROM timestamp_demo_table;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| 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 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

Copy

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

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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');

Copy

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

Copy

Subtrahiert 4 Tage von einem bestimmten Datum:

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

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

Copy

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;

Copy