DATE-Funktionen in Standard-SQL

BigQuery unterstützt die folgenden DATE-Funktionen.

CURRENT_DATE

CURRENT_DATE([time_zone])

Beschreibung

Gibt das aktuelle Datum in der angegebenen oder der Standardzeitzone zurück.

Diese Funktion unterstützt einen optionalen Parameter time_zone. Dieser Parameter ist ein String, der die zu verwendende Zeitzone darstellt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet. Siehe Zeitzonendefinitionen für Anweisungen, wie eine Zeitzone festgelegt wird.

Wenn der time_zone-Parameter NULL auswertet, gibt diese Funktion NULL zurück.

Rückgabedatentyp

DATE

Beispiel

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Beschreibung

Gibt den Wert zurück, der dem angegebenen Datumsteil entspricht. part muss einen der folgenden Werte haben:

  • DAYOFWEEK: gibt Werte im Bereich [1,7] mit Sonntag als dem ersten Tag der Woche zurück.
  • DAY
  • DAYOFYEAR
  • WEEK: gibt die Kalenderwoche des Datums im Bereich [0, 53] zurück. Wochen beginnen mit Sonntag und Datumsangaben vor dem ersten Sonntag des Jahres liegen in Woche 0.

  • WEEK(<WEEKDAY>): gibt die Kalenderwoche des Datums im Bereich [0, 53] zurück. Wochen beginnen mit WEEKDAY. Datumsangaben vor dem ersten WEEKDAY des Jahres liegen in Woche 0. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.

  • ISOWEEK: gibt die Kalenderwoche nach ISO 8601 für date_expression zurück. ISOWEEKs beginnen mit Montag. Rückgabewerte liegen im Bereich [1, 53]. Die erste ISOWEEK-Woche eines ISO-Jahres beginnt mit dem Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres.

  • MONTH

  • QUARTER: gibt Werte im Bereich [1,4] zurück.

  • YEAR

  • ISOYEAR: gibt das Kalenderjahr mit nach ISO 8601 nummerierten Wochen zurück. Das entspricht dem gregorianischen Kalenderjahr mit dem Donnerstag der Woche, zu der date_expression gehört.

Rückgabedatentyp

INT64

Beispiele

Im folgenden Beispiel gibt EXTRACT einen Wert zurück, der DAY entspricht.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

Im folgenden Beispiel gibt EXTRACT Werte zurück, die verschiedenen Teilen der Terminangabe aus einer Spalte von Terminen rund um das Jahresende entsprechen.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

Im folgenden Beispiel fällt date_expression auf einen Sonntag. EXTRACT berechnet die erste Spalte mit Wochen, die am Sonntag beginnen, und die zweite Spalte mit Wochen, die am Montag beginnen.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Beschreibung

  1. Konstruiert ein DATE aus INT64-Werten, die das Jahr, den Monat und den Tag repräsentieren.
  2. Wandelt einen timestamp_expression in einen DATE-Datentyp um. Ein optionaler Parameter zur Angabe einer Zeitzone wird unterstützt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet.

Rückgabedatentyp

DATE

Beispiel

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

Beschreibung

Fügt einem DATE ein bestimmtes Zeitintervall hinzu.

DATE_ADD unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum am (oder in der Nähe des) letzten Tags des Monats liegt. Wenn der daraus resultierende Monat weniger Tage hat als der Monat des ursprünglichen Datums, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

Beschreibung

Subtrahiert ein bestimmtes Zeitintervall von einem DATE.

DATE_SUB unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum am (oder in der Nähe des) letzten Tags des Monats liegt. Wenn der daraus resultierende Monat weniger Tage hat als der Monat des ursprünglichen Datums, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

Beschreibung

Gibt die Anzahl der date_part-Grenzwerte zwischen den beiden Datumsausdrücken (date_expressions) zurück. Wenn das erste Datum vor dem zweiten Datum liegt, ist das Ergebnis nicht positiv.

DATE_DIFF unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK: Dieser Datumsteil beginnt mit Sonntag.
  • WEEK(<WEEKDAY>): Dieser Datumsteil beginnt mit WEEKDAY. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: verwendet Wochengrenzen nach ISO 8601. ISO-Wochen beginnen mit Montag.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: verwendet die Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

INT64

Beispiel

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

Im obigen Beispiel wird das Ergebnis von DATE_DIFF für zwei aufeinanderfolgende Tage dargestellt. DATE_DIFF mit dem Datumsteil WEEK gibt 1 zurück, da von DATE_DIFF die Anzahl der Datumsteilgrenzen in diesem Datumsbereich gezählt wird. Jede WEEK beginnt mit Sonntag. Deshalb gibt es eine Datumsteilgrenze zwischen Samstag, dem 14.10.2017, und Sonntag, dem 15.10.2017.

Im folgenden Beispiel wird das Ergebnis von DATE_DIFF für zwei Datumsangaben in verschiedenen Jahren dargestellt. DATE_DIFF mit dem Datumsteil YEAR gibt 3 zurück, da die Anzahl der Grenzen zwischen gregorianischen Kalenderjahren zwischen den beiden Datumsangaben gezählt wird. DATE_DIFF mit dem Datumsteil ISOYEAR gibt 2 zurück, da das zweite Datum zum ISO-Jahr 2015 zählt. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

Im folgenden Beispiel wird das Ergebnis von DATE_DIFF für zwei aufeinanderfolgende Tage dargestellt. Das erste Datum fällt auf einen Montag, das zweite auf einen Sonntag. DATE_DIFF mit dem Datumsteil WEEK gibt 0 zurück, da von diesem Datumsteil Wochen verwendet werden, die mit Sonntag beginnen. DATE_DIFF mit dem Datumsteil WEEK(MONDAY) gibt 1 zurück. DATE_DIFF mit dem Datumsteil ISOWEEK gibt ebenfalls 1 zurück, da ISO-Wochen mit Montag beginnen.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Beschreibung

Kürzt das Datum auf die angegebene Granularität.

DATE_TRUNC unterstützt die folgenden Werte für date_part:

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): kürzt date_expression auf die vorhergehende Wochengrenze, wobei Wochen mit WEEKDAY beginnen. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: kürzt date_expression auf die vorhergehende Wochengrenze nach ISO 8601. ISOWEEK-Wochen beginnen mit Montag. Die erste ISOWEEK-Woche eines ISO-Jahres enthält den ersten Donnerstag des entsprechenden gregorianischen Kalenderjahres. Jede date_expression vor diesem Datum wird auf den vorhergehenden Montag gekürzt.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: kürzt date_expression auf die vorhergehende Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

DATE

Beispiele

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

Im folgenden Beispiel fällt das ursprüngliche Datum auf einen Sonntag. Da es sich bei date_part um WEEK(MONDAY) handelt, gibt DATE_TRUNC das DATE für den vorhergehenden Montag zurück.

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

Im folgenden Beispiel liegt der ursprüngliche date_expression im gregorianischen Kalenderjahr 2015. DATE_TRUNC mit dem Datumsteil ISOYEAR kürzt date_expression jedoch bis zum Anfang des ISO-Jahres und nicht des gregorianischen Kalenderjahres. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014. Entsprechend fällt die ISO-Jahresgrenze vor date_expression 2015-06-15 auf den 29.12.2014.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

Beschreibung

Interpretiert INT64_expression als Anzahl der Tage seit 01.01.1970.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Beschreibung

Formatiert date_expr entsprechend dem angegebenen format_string.

Unter Unterstützte Formatelemente für DATE ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiel

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Beschreibung

Verwendet für die Rückgabe eines DATE-Objekts einen format_string und eine Stringdarstellung eines Datums.

Bei der Verwendung von PARSE_DATE bitte Folgendes beachten:

  • Nicht spezifizierte Felder. Jedes unspezifizierte Feld wird ab 1970-01-01 initialisiert.
  • Groß-/Kleinschreibung kann ignoriert werden. Bei Namen, wie Monday, February und so weiter, kann die Groß-/Kleinschreibung ignoriert werden.
  • Leerraum. Ein oder mehrere aufeinanderfolgende Leerräume im Formatstring stimmen mit null oder mehreren aufeinanderfolgenden Leerräumen im Datumsstring überein. Darüber hinaus sind voran- und nachgestellte Leerräume im Datumsstring immer erlaubt­­ – auch wenn sie nicht im Formatstring vorhanden sind.
  • Formatvorrang. Wenn zwei (oder mehrere) Formatelemente überlappende Informationen aufweisen (z. B. %F und %Y beeinflussen beide das Jahr), dann überschreibt im Allgemeinen das letzte Format alle vorherigen Formate.

Unter Unterstützte Formatelemente für DATE ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

DATE

Beispiel

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Beschreibung

Gibt die Anzahl der Tage seit 01.01.1970 zurück.

Rückgabedatentyp

INT64

Beispiel

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Unterstützte Formatelemente für DATE

Wenn nicht anders angegeben, unterstützen DATE-Funktionen, die Formatstrings verwenden, folgende Elemente:

Formatelement Beschreibung
%A Der vollständige Wochentagname.
%a Der abgekürzte Wochentagname.
%B Der vollständige Monatsname.
%b oder %h Der abgekürzte Monatsname.
%C Das Jahrhundert (ein Jahr geteilt durch 100 und gekürzt auf eine Ganzzahl) als Dezimalzahl (00–99).
%D Das Datum im Format %m/%d/%y.
%d Der Tag des Monats als Dezimalzahl (01–31).
%e Der Tag des Monats als Dezimalzahl (1–31); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%F Das Datum im Format %Y-%m-%d.
%G Das Jahr nach ISO 8601 mit Jahrhundert als Dezimalzahl. Jedes ISO-Jahr beginnt mit dem Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres. Beachten Sie, dass %G und %Y nahe den gregorianischen Jahresgrenzen unterschiedliche Ergebnisse erzeugen können, wobei das gregoriansiche Jahr und ISO-Jahr von einander abweichen können.
%g Das Jahr nach ISO 8601 ohne Jahrhundert als Dezimalzahl (00–99). Jedes ISO-Jahr beginnt mit dem Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres. Beachten Sie, dass %g und %y nahe den gregorianischen Jahresgrenzen unterschiedliche Ergebnisse erzeugen können, wobei das gregoriansiche Jahr und ISO-Jahr von einander abweichen können.
%j Der Tag des Jahres als Dezimalzahl (001–366).
%m Der Monat als Dezimalzahl (01–12).
%n Ein Zeilenumbruch.
%t Ein Tab-Zeichen.
%U Die Wochennummer des Jahres (Sonntag als erster Tag der Woche) als Dezimalzahl (00–53).
%u Der Wochentag (Montag als erster Tag der Woche) als Dezimalzahl (1–7).
%V Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (01–53). Wenn die Woche einschließlich 1. Januar mindestens vier Tage im neuen Jahr aufweist, dann ist es Woche 1; andernfalls ist es Woche 53 des Vorjahres und die nächste Woche ist Woche 1.
%W Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (00–53).
%w Der Wochentag (Sonntag als erster Tag der Woche) als Dezimalzahl (0–6).
%x Die Datumsdarstellung im MM/TT/JJ-Format.
%Y Das Jahr mit Jahrhundert als Dezimalzahl.
%y Das Jahr ohne Jahrhundert als Dezimalzahl (00–99), mit einer optionalen vorangestellten Null. Kann mit %C gemischt werden. Wenn %C nicht angegeben ist, sind die Jahre 00–68 2000er, während die Jahre 69–99 1900er sind.
%E4Y Jahre mit vier Ziffern (0001... 9999). Beachten Sie, dass %Y so viele Zeichen produziert, wie nötig sind, um das Jahr komplett darzustellen.
Hat Ihnen diese Seite weitergeholfen? Teilen Sie uns Ihr Feedback mit:

Feedback geben zu...