Datetime-Funktionen in Standard SQL

BigQuery unterstützt die folgenden DATETIME Funktionen.

Alle Ausgaben werden automatisch gemäß ISO 8601 formatiert, wobei Datum und Uhrzeit durch einT getrennt werden.

CURRENT_DATETIME

CURRENT_DATETIME()

Beschreibung

Gibt die aktuelle Zeit als DATETIME Objekt zurück.

Diese Funktion unterstützt einen optionalen timezone Parameter.Siehe Zeitzonendefinitionen für Informationen zur Angabe einer Zeitzone.

Rückgabe Datentyp

DATETIME

Beispiel

SELECT CURRENT_DATETIME() as now;+----------------------------+| now |+----------------------------+| 2016-05-19T10:38:47.046465 |+----------------------------+

DATETIME

Beschreibung

1. Erzeugt ein DATETIMEObjekt mit INT64-Werten, die Jahr, Monat, Tag, Stunde, Minute und Sekunde darstellen.
2. Konstruiert ein DATETIME-Objekt mit einem DATE-Objekt und einem optionalen TIME-Objekt.
3. Konstruiert ein DATETIME-Objekt mit einem TIMESTAMP-Objekt. Es unterstützt einen optionalen Parameter zur Angabe einer Zeitzone. Wenn keine Zeitzone angegeben wird, wird die Standardzeitzone UTC verwendet.

Rückgabe des Datentyps

DATETIME

Beispiel

EXTRACT

EXTRACT(part FROM datetime_expression)

Beschreibung

Gibt einen Wert zurück, der dem angegebenen part aus einem übergebenen datetime_expression entspricht.

Erlaubte part Werte sind:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK: Liefert die Wochennummer des Datums im Bereich . Wochen beginnen mit dem Sonntag, und Daten vor dem ersten Sonntag des Jahres sind in Woche 0.
• WEEK(<WEEKDAY>): Gibt die Wochennummer von datetime_expression im Bereich zurück. Die Wochen beginnen am WEEKDAY.datetimeDaten, die vor dem ersten WEEKDAY des Jahres liegen, sind in der Woche 0. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY,THURSDAY, FRIDAY, und SATURDAY.
• ISOWEEK: Gibt die ISO 8601-Wochennummer des datetime_expression zurück. ISOWEEKs beginnen am Montag. Die Rückgabewerte liegen im Bereich . Das erste ISOWEEK eines jeden ISO-Jahres beginnt am Montag vor dem ersten Donnerstag des Gregorianischen Kalenderjahres.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Gibt das Jahr mit ISO 8601-Wochennummerierung zurück, d. h. das gregorianische Kalenderjahr, das den Donnerstag der Woche enthält, zu der date_expression gehört.
• DATE
• TIME

Die zurückgegebenen Werte schneiden Zeiträume niedriger Ordnung ab. Wenn beispielsweise Sekunden extrahiert werden, schneidet EXTRACT die Werte für Milli- und Mikrosekunden ab.

Datentyp zurückgeben

INT64, außer in den folgenden Fällen:

• Wenn part DATE ist, wird ein DATE-Objekt zurückgegeben.
• Wenn part TIME ist, wird ein TIME-Objekt zurückgegeben.

Beispiele

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

SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;+------------------+| hour |+------------------+| 15 |+------------------+

Im folgenden Beispiel gibt EXTRACT Werte zurück, die verschiedenen Zeitteilen aus einer Spalte von Datumsangaben entsprechen.

Im folgenden Beispiel fällt datetime_expression auf einen Sonntag. EXTRACTberechnet die erste Spalte unter Verwendung von Wochen, die am Sonntag beginnen, und die zweite Spalte unter Verwendung von Wochen, die am Montag beginnen.

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

Beschreibung

Fügt int64_expression Einheiten von part zum Objekt DATETIME hinzu.

DATETIME_ADD unterstützt die folgenden Werte für part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Entspricht 7 DAYs.
• MONTH
• QUARTER
• YEAR

Eine besondere Behandlung ist für die Teile MONAT, QUARTIER und JAHR erforderlich, wenn das Datum am (oder nahe am) letzten Tag des Monats liegt. Wenn der resultierende Monat weniger Tage hat als der Tag der ursprünglichen DATETIME, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATETIME

Beispiel

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

Beschreibung

Subtrahiert int64_expression Einheiten von part von der DATETIME.

DATETIME_SUB unterstützt die folgenden Werte für part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Entspricht 7 DAYs.
• MONTH
• QUARTER
• YEAR

Eine besondere Behandlung ist für die Teile MONTH, QUARTER und YEAR erforderlich, wenn das Datum am (oder nahe dem) letzten Tag des Monats liegt. Wenn der resultierende Monat weniger Tage hat als der ursprüngliche DATETIME-Tag, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATETIME

Beispiel

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

Beschreibung

Retourniert die Anzahl ganzer angegebener part Intervalle zwischen zwei DATETIME Objekten (datetime_expression_a – datetime_expression_b).Wenn das erste DATETIME vor dem zweiten liegt, ist die Ausgabe negativ. Wirft einen Fehler, wenn die Berechnung den Ergebnistyp überläuft, z.B. wenn die Differenz in Mikrosekunden zwischen den beiden DATETIME-Objekten einenINT64-Wert überlaufen lassen würde.

DATETIME_DIFF unterstützt die folgenden Werte für part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: Dieser Datumsteil beginnt am Sonntag.
• WEEK(<WEEKDAY>): Dieser Datumsteil beginnt am WEEKDAY. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY, und SATURDAY.
• ISOWEEK: Verwendet ISO 8601 Wochengrenzen. ISO-Wochen beginnen am Montag.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Verwendet die Jahresgrenze der ISO 8601-Wochennummerierung. Die ISO-Jahresgrenze ist der Montag der ersten Woche, deren Donnerstag zum entsprechenden gregorianischen Kalenderjahr gehört.

Rückgabedatentyp

INT64

Beispiel

Das obige Beispiel zeigt das Ergebnis von DATETIME_DIFF für zwei DATETIMEs, die 24 Stunden auseinander liegen. DATETIME_DIFF mit dem Teil WEEK gibt 1 zurück, weilDATETIME_DIFF die Anzahl der Teilgrenzen in diesem Bereich vonDATETIMEs zählt. Jedes WEEK beginnt am Sonntag, also gibt es eine Teilgrenze zwischen Samstag, 2017-10-14 00:00:00 und Sonntag, 2017-10-15 00:00:00.

Das folgende Beispiel zeigt das Ergebnis von DATETIME_DIFF für zwei Datumsangaben aus unterschiedlichen Jahren. DATETIME_DIFF mit dem Datumsteil YEAR ergibt 3, weil es die Anzahl der gregorianischen Kalenderjahresgrenzen zwischen den beidenDATETIMEs zählt. DATETIME_DIFF mit dem Datumsteil ISOYEAR ergibt 2, weil dieses zweite DATETIME zum ISO-Jahr 2015 gehört. Der erste Donnerstag des Kalenderjahres 2015 war der 2015-01-01, also beginnt das ISO-Jahr 2015 am vorangehenden Montag, dem 2014-12-29.

Das folgende Beispiel zeigt das Ergebnis von DATETIME_DIFF für zwei aufeinanderfolgende Tage. Das erste Datum fällt auf einen Montag und das zweite Datum auf einen Sonntag. DATETIME_DIFF mit dem Datumsteil WEEK ergibt 0, da dieser Zeitteil Wochen verwendet, die am Sonntag beginnen. DATETIME_DIFF mit dem DatumsteilWEEK(MONDAY) ergibt 1. DATETIME_DIFF mit dem DatumsteilISOWEEK gibt ebenfalls 1 zurück, weil ISO-Wochen am Montag beginnen.

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Beschreibung

Truncates a DATETIME object to the granularity of part.

DATETIME_TRUNC unterstützt die folgenden Werte für part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): Schneidet datetime_expression auf die vorangehende Wochengrenze ab, wobei die Wochen am WEEKDAY beginnen. Gültige Werte für WEEKDAY sindSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY undSATURDAY.
• ISOWEEK: Kürzt datetime_expression auf die vorangehendeISO 8601-Wochengrenze ab. ISOWEEKbeginnen am Montag. Der erste ISOWEEK eines jeden ISO-Jahres enthält den ersten Donnerstag des entsprechenden Gregorianischen Kalenderjahres. Jeder date_expressionfrühere Tag wird auf den vorangehenden Montag abgeschnitten.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Schneidet datetime_expression auf die vorangehende ISO 8601-Wochennummerierung ab. Die ISO-Jahresgrenze ist der Montag der ersten Woche, deren Donnerstag zum entsprechenden gregorianischen Kalenderjahr gehört.

Datentyp zurückgeben

DATETIME

Beispiele

Im folgenden Beispiel fällt die ursprüngliche DATETIME auf einen Sonntag. Da daspart WEEK(MONDAY) ist, liefert DATE_TRUNC das DATETIME für den vorangehenden Montag.

Im folgenden Beispiel ist das Original datetime_expression im gregorianischen Kalenderjahr 2015. Durch DATETIME_TRUNC mit dem Datumsteil ISOYEAR wird datetime_expression jedoch auf den Beginn des ISO-Jahres und nicht des gregorianischen Kalenderjahres abgeschnitten. Der erste Donnerstag des Kalenderjahres 2015 war der2015-01-01, so dass das ISO-Jahr 2015 am vorhergehenden Montag, dem 2014-12-29, beginnt.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Beschreibung

Formatiert ein DATETIME-Objekt gemäß dem angegebenen format_string. Siehe Unterstützte Formatelemente für DATETIME für eine Liste der Formatelemente, die diese Funktion unterstützt.

Rückgabe Datentyp

STRING

Beispiele

SELECT FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00") AS formatted;+--------------------------+| formatted |+--------------------------+| Thu Dec 25 15:30:00 2008 |+--------------------------+

SELECT FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec-25-2008 |+-------------+

SELECT FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec 2008 |+-------------+

LAST_DAY

LAST_DAY(datetime_expression)

Beschreibung

Gibt den letzten Tag aus einem datetime-Ausdruck zurück, der das Datum enthält.Dies wird üblicherweise verwendet, um den letzten Tag des Monats zurückzugeben.

Sie können optional den Datumsteil angeben, für den der letzte Tag zurückgegeben wird.LAST_DAY Wenn dieser Parameter nicht verwendet wird, ist der Standardwert MONTH.LAST_DAY unterstützt die folgenden Werte für date_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Äquivalent zu 7 DAYs.
• WEEK(<WEEKDAY>). <WEEKDAY> steht für den Anfangstag der Woche; gültige Werte sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY und SATURDAY.
• ISOWEEK. Verwendet ISO 8601 Wochengrenzen. ISO-Wochen beginnen am Montag.
• ISOYEAR. Die ISO-Jahresgrenze ist der Montag der ersten Woche, deren Donnerstag zu dem entsprechenden gregorianischen Kalenderjahr gehört.

Rückgabedatentyp

DATE

Beispiel

Diese beiden geben den letzten Tag des Monats zurück:

SELECT LAST_DAY(DATETIME '2008-11-25', MONTH) AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+

SELECT LAST_DAY(DATETIME '2008-11-25') AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+

Dies gibt den letzten Tag des Jahres zurück:

SELECT LAST_DAY(DATETIME '2008-11-25 15:30:00', YEAR) AS last_day+------------+| last_day |+------------+| 2008-12-31 |+------------+

Dies gibt den letzten Tag der Woche für eine Woche zurück, die an einem Sonntag beginnt:

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-15 |+------------+

Dies liefert den letzten Tag der Woche für eine Woche, die an einem Montag beginnt:

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-16 |+------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, datetime_string)

Beschreibung

Wandelt eine String-Darstellung einer Datetime in einDATETIME-Objekt um.

format_string enthält die Formatelemente, die festlegen, wie datetime_string formatiert wird. Jedes Element indatetime_string muss ein entsprechendes Element in format_string haben. Die Position jedes Elements in format_string muss mit der Position jedes Elements in datetime_string übereinstimmen.

Die Formatzeichenfolge unterstützt die meisten Formatelemente vollständig, mit Ausnahme von%Q, %a, %A,%g, %G, %j, %P, %u, %U, %V, %w und %W.

PARSE_DATETIME parst string nach den folgenden Regeln:

• Nicht spezifizierte Felder. Jedes nicht spezifizierte Feld wird ab1970-01-01 00:00:00.0 initialisiert. Wenn z. B. das Jahr nicht angegeben ist, wird es mit 1970 initialisiert.
• Groß- und Kleinschreibung wird nicht berücksichtigt. Bei Namen wie Monday und February wird die Groß- und Kleinschreibung nicht berücksichtigt.
• Leerzeichen. Ein oder mehrere aufeinanderfolgende Leerzeichen in der Formatzeichenfolge entsprechen null oder mehreren aufeinanderfolgenden Leerzeichen in der Zeichenfolge DATETIME. Führende und abschließende Leerzeichen in der Zeichenfolge DATETIME sind immer zulässig, auch wenn sie nicht in der Formatzeichenfolge enthalten sind.
• Vorrang des Formats. Wenn zwei oder mehr Formatelemente sich überschneidende Informationen haben, hat das letzte Element im Allgemeinen Vorrang vor allen früheren, mit einigen Ausnahmen. Beispielsweise betreffen sowohl %F als auch %Y das Jahr, so dass das frühere Element das spätere überschreibt. Siehe die Beschreibungen von %s, %C und %y inUnterstützte Formatelemente für DATETIME.

Datentyp zurückgeben

DATETIME

Beispiele

Die folgenden Beispiele analysieren ein STRING-Literal als einDATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;+---------------------+| datetime |+---------------------+| 1998-10-18T13:45:55 |+---------------------+

SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime+---------------------+| datetime |+---------------------+| 2018-08-30T14:23:38 |+---------------------+

Das folgende Beispiel parst ein STRING-Literal, das ein Datum in einem natürlichsprachlichen Format enthält, als aDATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018') AS datetime;+---------------------+| datetime |+---------------------+| 2018-12-19T00:00:00 |+---------------------+

Unterstützte Formatelemente für DATETIME

Wenn nicht anders angegeben, unterstützen DATETIME-Funktionen, die Format-Strings verwenden, die folgenden Elemente: