Datums- und Uhrzeitfunktionen

Diese Funktionsfamilie kann zum Erstellen, Konvertieren, Extrahieren oder Ändern von DATE/TIME/TIMESTAMP-Daten verwendet werden.

Unter diesem Thema:

Liste der Funktionen

Unterkategorie

Funktion

Anmerkungen

Erstellen

DATE_FROM_PARTS

TIME_FROM_PARTS

TIMESTAMP_FROM_PARTS

Extrahieren

DATE_PART

Akzeptiert alle Datums- und Zeitabschnitte (siehe nächsten Abschnitt für Details).

DAYNAME

EXTRACT

Alternative zu DATE_PART.

HOUR / MINUTE / SECOND

Alternative zu DATE_PART.

LAST_DAY

Akzeptiert relevante Datumsabschnitte (siehe nächsten Abschnitt für Details).

MONTHNAME

NEXT_DAY

PREVIOUS_DAY

YEAR* / DAY* / WEEK* / MONTH / QUARTER

Alternative zu DATE_PART.

Hinzufügen/Abziehen

ADD_MONTHS

DATEADD

Akzeptiert alle relevanten Datums- und Zeitabschnitte (siehe nächsten Abschnitt für Details).

DATEDIFF

Akzeptiert alle relevanten Datums- und Zeitabschnitte (siehe nächsten Abschnitt für Details).

TIMEADD

Alias für DATEADD.

TIMEDIFF

Alias für DATEDIFF.

TIMESTAMPADD

Alias für DATEADD.

TIMESTAMPDIFF

Alias für DATEDIFF.

Kürzen

DATE_TRUNC

Akzeptiert alle relevanten Datums- und Zeitabschnitte (siehe nächsten Abschnitt für Details).

TIME_SLICE

Ermöglicht das „Runden“ einer Zeit auf den Beginn eines gleichmäßig verteilten Intervalls.

TRUNC

Alternative zu DATE_TRUNC.

Konvertieren

TO_DATE , DATE

TO_TIME , TIME

TO_TIMESTAMP / TO_TIMESTAMP_*

Zeitzone

CONVERT_TIMEZONE

Unterstützte Datums- und Zeitabschnitte

Bestimmte Funktionen (sowie die entsprechenden Aliasse und Alternativen) akzeptieren eine Datums- oder Uhrzeitkomponente als Argument. In den folgenden beiden Tabellen sind die Abschnitte (Groß-/Kleinschreibung wird nicht berücksichtigt) aufgeführt, die mit diesen Funktionen verwendet werden können.

Datumskomponenten

Abkürzungen/Varianten

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

year

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

month

mm , mon , mons , months

day

d , dd , days, dayofmonth

dayofweek [1]

weekday , dow , dw

dayofweekiso [2]

weekday_iso , dow_iso , dw_iso

dayofyear

yearday , doy , dy

week [1]

w , wk , weekofyear , woy , wy

weekiso [2]

week_iso , weekofyeariso , weekofyear_iso

quarter

q , qtr , qtrs , quarters

yearofweek [1]

yearofweekiso [2]

[1] Details zur Nutzung finden Sie im nächsten Abschnitt, in dem beschrieben wird, wie Snowflake mit Kalenderwochen und Wochentagen umgeht.

[2] Nicht durch die Sitzungsparameter WEEK_START und WEEK_OF_YEAR_POLICY gesteuert, wie im nächsten Abschnitt beschrieben.

Uhrzeitkomponenten

Abkürzungen/Varianten

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

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

epoch_second

epoch , epoch_seconds

epoch_millisecond

epoch_milliseconds

epoch_microsecond

epoch_microseconds

epoch_nanosecond

epoch_nanoseconds

timezone_hour

tzh

timezone_minute

tzm

Kalenderwochen und Wochentage

Das Verhalten wochenbezogener Funktionen in Snowflake wird durch die Sitzungsparameter WEEK_START und WEEK_OF_YEAR_POLICY gesteuert. Ein wichtiger Aspekt zum Verstehen, wie diese Parameter zusammenwirken, ist das Konzept der ISO-Wochen.

ISO-Wochen

Wie im ISO 8601-Standard (für Datums- und Uhrzeitformate) definiert, beginnen ISO-Wochen immer am Montag und „gehören“ zu dem Jahr, in dem sich der Donnerstag dieser Woche befindet. Das bedeutet, dass ein Tag in einem Jahr zu einer Woche eines anderen Jahres gehören kann:

  • Für Tage Anfang Januar kann der WOY-Wert (Woche des Jahres) 52 oder 53 betragen (d. h. der Tag gehört zur letzten Woche des Vorjahres).

  • Für Tage Ende Dezember kann der WOY-Wert 1 sein (d. h. der Tag gehört zur ersten Woche des nächsten Jahres).

Snowflake bietet einen speziellen Satz wochenbezogener Datumsfunktionen (und äquivalente Datumskomponenten), deren Verhalten mit der ISO-Wochensemantik übereinstimmt: DAYOFWEEKISO, WEEKISO, YEAROFWEEKISO.

Diese Funktionen (und Datumskomponenten) ignorieren die Sitzungsparameter (d. h. sie folgen immer der ISO-Semantik).

Weitere Details zur Behandlung aller anderen wochenbezogenen Datumsfunktionen finden Sie in den nächsten Abschnitten (unter diesem Thema).

Erster Tag der Woche

Die meisten wochenbezogenen Funktionen werden nur vom Sitzungsparameter WEEK_START gesteuert. Beachten Sie, dass sich die Funktionsergebnisse je nach Einstellung des Parameters unterscheiden:

Funktion

Parameter auf 0 gesetzt (Standard-/Legacy-Verhalten)

Parameter auf 1 - 7 gesetzt (Montag–Sonntag)

DAY und DAYOFWEEK

Gibt 0 (Sonntag) bis 6 (Samstag) zurück.

Gibt 1 (definierter erster Tag der Woche) bis 7 (letzter Tag der Woche bezogen auf den definierten ersten Tag) zurück.

DATE_TRUNC (mit einem WEEK-Abschnitt)

Kürzt die Eingabewoche, sodass sie am Montag beginnt.

Kürzt die Eingabewoche, sodass sie am definierten ersten Tag der Woche beginnt.

LAST_DAY (mit einem WEEK-Abschnitt)

Gibt den Sonntag der Eingabewoche zurück.

Gibt den letzten Tag der Eingabewoche bezogen auf den definierten ersten Tag der Woche zurück.

DATEDIFF (mit einem WEEK-Abschnitt)

Führt Berechnungen mit Wochen aus, die am Montag beginnen.

Berechnet mit Wochen, die am definierten ersten Tag der Woche beginnen.

Tipp

Der Standardwert für den Parameter ist 0, sodass das Legacy-Snowflake-Verhalten (ISO-ähnliche Semantik) beibehalten wird. Wir empfehlen jedoch, diesen Wert zu ändern, um das resultierende Verhalten der Funktionen explizit zu steuern. Basierend auf dem Feedback, das wir erhalten haben, ist das häufigste Szenario, den Parameter auf 1 zu setzen.

Erste/letzte Woche des Jahres

Der Sitzungsparameter WEEK_OF_YEAR_POLICY steuert, wie sich die Funktionen WEEK und YEAROFWEEK verhalten. Der Parameter kann zwei Werte haben:

  • 0: Die betroffenen wochenbezogenen Funktionen verwenden eine Semantik ähnlich der ISO-Semantik, bei der eine Woche zu einem bestimmten Jahr gehört, wenn mindestens 4 Tage dieser Woche in diesem Jahr liegen. Das bedeutet, dass alle Wochen 7 Tage haben, aber die ersten Tage im Januar und die letzten Tage im Dezember können zu einer Woche in einem anderen Jahr gehören. Aus diesem Grund können sowohl die Funktion YEAROFWEEK als auch YEAROFWEEKISO das Jahr angeben, zu dem die Woche gehört.

  • 1: Der 1. Januar beginnt immer in der ersten Woche des Jahres, und der 31. Dezember liegt immer in der letzten Woche des Jahres. Das bedeutet, dass die erste Woche und die letzte Woche des Jahres weniger als 7 Tage haben können.

Beachten Sie, dass dieses Verhalten auch vom Starttag der Woche beeinflusst wird, der durch den für den Sitzungsparameter WEEK_START festgelegten Wert gesteuert wird:

  • 0 , 1: Das Verhalten entspricht der ISO-Wochensemantik, wobei die Woche am Montag beginnt.

  • 2 bis 7: Die 4-Tage-Logik bleibt erhalten, aber der erste Tag der Woche ist anders.

Tipp

Der Standardwert für beide Parameter ist 0, sodass das Legacy-Snowflake-Verhalten (ISO-ähnliche Semantik) beibehalten wird. Wir empfehlen jedoch, diese Werte zu ändern, um das resultierende Verhalten der Funktionen explizit zu steuern. Basierend auf dem Feedback, das wir erhalten haben, ist das häufigste Szenario, beide Parameter auf 1 zu setzen.

Beispiele

Bemerkung

In diesen Beispielen wird der gleiche Satz von Datumsfunktionen abgefragt, wobei jedoch für die Sitzungsparameter WEEK_OF_YEAR_POLICY und WEEK_START unterschiedliche Werte festgelegt sind, um zu veranschaulichen, wie sie die Ergebnisse der Funktionen beeinflussen.

Festlegen des ersten Tages der Woche

WEEK_START auf 0 (Legacy-Verhalten) oder 1 (Montag) zu setzen, hat keinen signifikanten Effekt, wie die folgenden beiden Beispiele zeigen:

ALTER SESSION SET WEEK_START = 0;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   0 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   0 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

ALTER SESSION SET WEEK_START = 1;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   7 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   7 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
  1. Wenn WEEK_START auf 0 gesetzt ist, ist der DOW für Sonntag 0.

  2. Wenn WEEK_START auf 1 gesetzt ist, ist der DOW für Sonntag 7.

Die Ergebnisse unterscheiden sich deutlicher, wenn WEEK_START auf einen anderen Tag als Montag gesetzt wird. Wenn Sie beispielsweise den Parameter auf 3 (Mittwoch) setzen, werden die Ergebnisse aller wochenbezogenen Funktionen (Spalten 3 bis 8) geändert:

ALTER SESSION SET WEEK_START = 3;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   3 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2016-12-31 | Sat |   4 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-01 | Sun |   5 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-02 | Mon |   6 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-03 | Tue |   7 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-04 | Wed |   1 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-01-05 | Thu |   2 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-12-30 | Sat |   4 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
| 2017-12-31 | Sun |   5 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

Festlegen des Jahres und der Tage für die ersten/letzten Wochen des Jahres

Im folgenden Beispiel werden beide Parameter auf 0 gesetzt, um ISO-ähnlicher Semantik zu folgen (d. h. Woche beginnt am Montag und alle Wochen haben 7 Tage):

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=0;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  52 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  52 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  52 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

Das nächste Beispiel veranschaulicht den Effekt, wenn WEEK_OF_YEAR_POLICY auf 0 gesetzt bleibt, aber WEEK_START auf 3 (Mittwoch) geändert wird:

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  53 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |  53 |         1 | 2016 |      2017 |
| 2017-01-03 | Tue |  53 |         1 | 2016 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  • 2016 hat jetzt 53 Wochen (statt 52).

  • WOY für den 1. Januar 2017 wechselt zu Woche 53 (von 52).

  • WOY für den 2. und 3. Januar 2017 wechselt zu Woche 53 (von 1).

  • YOW für den 2. und 3. Januar 2017 wechselt zu 2016 (von 2017).

  • WOY (ISO) und YOW (ISO) sind von der Parameteränderung nicht betroffen.

Die letzten beiden Beispiele setzen WEEK_OF_YEAR_POLICY auf 1 und WEEK_START zunächst auf 1 (Montag) und dann auf 3 (Mittwoch):

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=1, WEEK_START=1;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   2 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   2 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

alter session set week_of_year_policy=1, week_start=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  1. Wenn WEEK_OF_YEAR_POLICY auf 1 und WEEK_START auf 1 (Montag) gesetzt ist:

    • WOY für 2017-01-01 ist 1.

    • Woche 1 besteht aus 1 Tag.

    • Woche 2 beginnt am Mon.

    Beachten Sie, dass dies das häufigste Nutzungsszenario ist, basierend auf dem Feedback, das wir erhalten haben.

  2. Wenn WEEK_OF_YEAR_POLICY auf 1 und WEEK_START auf 3 (Mittwoch) gesetzt ist:

    • WOY für den 01.01.2017 ist immer noch 1.

    • Woche 1 besteht aus 3 Tagen.

    • Woche 2 beginnt am Wed.

In beiden Beispielen sind WOY (ISO) und YOW (ISO) von der Parameteränderung nicht betroffen.