Funkcje datetime w standardowym SQL

BigQuery obsługuje następujące funkcje DATETIME.

Wszystkie dane wyjściowe są automatycznie formatowane zgodnie z ISO 8601, oddzielając datę i czas znakiemT.

CURRENT_DATETIME

CURRENT_DATETIME()

Opis

Przywraca bieżący czas jako obiekt DATETIME.

Ta funkcja obsługuje opcjonalny parametr timezone.Zobacz Definicje stref czasowych, aby uzyskać informacje o tym, jak określić strefę czasową.

Return Data Type

DATETIME

Przykład

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

DATETIME

Opis

1. Konstruuje obiekt DATETIME przy użyciu wartości INT64 reprezentujących rok, miesiąc, dzień, godzinę, minutę i sekundę.
2. Konstruuje obiekt DATETIME przy użyciu obiektu DATE i opcjonalnego obiektu TIME.
3. Konstruuje obiekt DATETIME przy użyciu obiektu TIMESTAMP. Obsługuje opcjonalny parametr do określenia strefy czasowej. Jeśli nie określono strefy czasowej, używana jest domyślna strefa czasowa, UTC.

Return Data Type

DATETIME

Example

EXTRACT

EXTRACT(part FROM datetime_expression)

Description

Zwraca wartość odpowiadającą określonemu part z dostarczonego datetime_expression.

Dozwolone wartości part to:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK: Zwraca numer tygodnia dla daty z zakresu . Tygodnie zaczynają się od niedzieli, a daty poprzedzające pierwszą niedzielę roku są w tygodniu 0.
• WEEK(<WEEKDAY>): Zwraca numer tygodnia datetime_expression w zakresie . Tygodnie zaczynają się w dniu WEEKDAY.datetimes przed pierwszą WEEKDAY roku są w tygodniu 0. Prawidłowe wartości dla WEEKDAY to SUNDAY, MONDAY, TUESDAY, WEDNESDAY,THURSDAY, FRIDAY i SATURDAY.
• ISOWEEK: Zwraca numer tygodnia ISO 8601 dla datetime_expression. ISOWEEK rozpoczyna się w poniedziałek. Zwracane wartości są z zakresu . Pierwszy ISOWEEK każdego roku ISO rozpoczyna się w poniedziałek przed pierwszym czwartkiem gregoriańskiego roku kalendarzowego.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Zwraca rok numeracji tygodnia ISO 8601, który jest gregoriańskim rokiem kalendarzowym zawierającym czwartek tygodnia, do którego należy date_expression.
• DATE
• TIME

Wrócone wartości obcinają okresy czasu niższego rzędu. Na przykład podczas wyodrębniania sekund, EXTRACT obcina wartości milisekund i mikrosekund.

Return Data Type

INT64, z wyjątkiem następujących przypadków:

• Jeśli part jest DATE, zwraca obiekt DATE.
• Jeśli part jest TIME, zwraca obiekt TIME.

Przykłady

W poniższym przykładzie EXTRACT zwraca wartość odpowiadającą części czasowej HOUR.

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

W poniższym przykładzie EXTRACT zwraca wartości odpowiadające różnym częściom czasowym z kolumny datetimes.

W poniższym przykładzie datetime_expression przypada na niedzielę. EXTRACTcalculates the first column using weeks that begin on Sunday, and it calculatesthe second column using weeks that begin on Monday.

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

Description

Dodaje int64_expressionjednostek part do obiektu DATETIME.

DATETIME_ADD obsługuje następujące wartości dla part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Równoważne 7 DAYs.
• MONTH
• QUARTER
• YEAR

Specjalna obsługa jest wymagana dla części MONTH, QUARTER i YEAR, gdy thedate jest na (lub w pobliżu) ostatniego dnia miesiąca. Jeżeli miesiąc wynikowy ma mniej dni niż dzień oryginalnego DATETIME, to dzień wynikowy jest ostatnim dniem nowego miesiąca.

Return Data Type

DATETIME

Przykład

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

Opis

Odejmuje int64_expression jednostek part od DATETIME.

DATETIME_SUB obsługuje następujące wartości dla part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Równoważne 7 DAYs.
• MONTH
• QUARTER
• YEAR

Specjalna obsługa jest wymagana dla części MONTH, QUARTER i YEAR, gdy data jest ostatniego dnia miesiąca (lub w jego pobliżu). Jeśli wynikowy miesiąc ma mniej dni niż dzień oryginalnego DATETIME, to wynikowy dzień jest ostatnim dniem nowego miesiąca.

Return Data Type

DATETIME

Przykład

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

Opis

Zwraca liczbę całych określonych part odstępów czasu między dwomaDATETIME obiektami (datetime_expression_a – datetime_expression_b).Jeśli pierwszy DATETIME jest wcześniejszy niż drugi, to wynik jest ujemny. Wyrzuca błąd, jeśli obliczenie przepełni typ wyniku, np. jeśli różnica mikrosekund między dwoma obiektami DATETIME przepełniłaby wartośćINT64.

DATETIME_DIFF obsługuje następujące wartości dla part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: Ta część daty zaczyna się w niedzielę.
• WEEK(<WEEKDAY>): Ta część daty zaczyna się w dniu WEEKDAY. Prawidłowe wartości dlaWEEKDAY to SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY i SATURDAY.
• ISOWEEK: Używa granic tygodniowych ISO 8601. Tygodnie ISO zaczynają się w poniedziałek.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Wykorzystuje granicę roku numeracji tygodniowej ISO 8601. Granicą roku ISO jest poniedziałek pierwszego tygodnia, którego czwartek należy do odpowiedniego roku kalendarza gregoriańskiego.

Return Data Type

INT64

Przykład

Powyższy przykład pokazuje wynik DATETIME_DIFF dla dwóch DATETIMEs, które są oddalone od siebie o 24 godziny. DATETIME_DIFF z częścią WEEK zwraca 1, ponieważ DATETIME_DIFF zlicza liczbę granic części w tym zakresieDATETIMEs. Każdy WEEK zaczyna się w niedzielę, więc istnieje jedna granica części między sobotą, 2017-10-14 00:00:00 i niedzielą, 2017-10-15 00:00:00.

Poniższy przykład pokazuje wynik działania funkcji DATETIME_DIFF dla dwóch dat z różnych lat. DATETIME_DIFF z częścią daty YEAR zwraca 3, ponieważ zlicza liczbę granic gregoriańskiego roku kalendarzowego między dwomaDATETIMEs. DATETIME_DIFF z częścią daty ISOYEAR zwraca 2, ponieważ drugi DATETIME należy do roku ISO 2015. Pierwszy czwartek roku kalendarzowego 2015 to 2015-01-01, więc rok ISO 2015 zaczyna się w poprzedzający poniedziałek, 2014-12-29.

Następny przykład pokazuje wynik DATETIME_DIFF dla dwóch dni nie następujących po sobie. Pierwsza data przypada na poniedziałek, a druga na niedzielę. DATETIME_DIFF z częścią daty WEEK zwraca 0, ponieważ ta część czasu używa tygodni, które zaczynają się w niedzielę. DATETIME_DIFF z częścią datyWEEK(MONDAY) zwraca 1. DATETIME_DIFF z częścią datyISOWEEK również zwraca 1, ponieważ tygodnie ISO rozpoczynają się w poniedziałek.

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Opis

Określa obiekt DATETIME do ziarnistości part.

DATETIME_TRUNC obsługuje następujące wartości dla part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): Obcina datetime_expression do poprzedzającej granicy tygodnia, gdzie tygodnie zaczynają się w dniu WEEKDAY. Prawidłowe wartości dla WEEKDAY toSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY iSATURDAY.
• ISOWEEK: Skraca datetime_expression do poprzedzającej granicy tygodniaISO 8601. ISOWEEKrozpoczyna się w poniedziałek. Pierwszy ISOWEEK każdego roku ISO zawiera pierwszy czwartek odpowiedniego gregoriańskiego roku kalendarzowego. Wszelkie date_expressionwcześniejsze niż to spowoduje obcięcie do poprzedzającego poniedziałku.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Obcina datetime_expression do poprzedzającej granicy tygodnia-numeru roku ISO 8601. Granicą roku ISO jest poniedziałek pierwszego tygodnia, którego czwartek należy do odpowiedniego roku kalendarza gregoriańskiego.

Return Data Type

DATETIME

Przykłady

W poniższym przykładzie oryginalny DATETIME przypada na niedzielę. Ponieważpart jest WEEK(MONDAY), DATE_TRUNC zwraca DATETIME dla poprzedzającego poniedziałku.

W poniższym przykładzie oryginalny datetime_expression jest w roku kalendarzowym gregoriańskim 2015. Jednak DATETIME_TRUNC z ISOYEAR data parttruncates the datetime_expression do początku roku ISO, nie theGregorian kalendarzowy rok. Pierwszym czwartkiem roku kalendarzowego 2015 był2015-01-01, więc rok ISO 2015 zaczyna się w poprzedzający poniedziałek, 2014-12-29.Dlatego granica roku ISO poprzedzająca datetime_expression2015-06-15 00:00:00 to 2014-12-29.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Opis

Formatuje obiekt DATETIME zgodnie z określonym format_string. Zobacz Obsługiwane elementy formatu dla DATETIME, aby uzyskać listę elementów formatu obsługiwanych przez tę funkcję.

Return Data Type

STRING

Przykłady

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)

Opis

Zwraca ostatni dzień z wyrażenia datetime zawierającego datę.Jest to powszechnie używane do zwracania ostatniego dnia miesiąca.

Opcjonalnie można określić część daty, dla której zwracany jest ostatni dzień.Jeśli ten parametr nie jest używany, domyślną wartością jest MONTH.LAST_DAY obsługuje następujące wartości dla date_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Równoważne 7 DAYs.
• WEEK(<WEEKDAY>). <WEEKDAY> przedstawia początkowy dzień tygodnia.prawidłowe wartości to SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY i SATURDAY.
• ISOWEEK. Wykorzystuje granice tygodni ISO 8601. Tygodnie ISO rozpoczynają się w poniedziałek.
• ISOYEAR. Używa granic roku z numeracją tygodni ISO 8601. Granicą roku ISO jest poniedziałek pierwszego tygodnia, którego czwartek należy do odpowiedniego gregoriańskiego roku kalendarzowego.

Return Data Type

DATE

Przykład

Oba zwracają ostatni dzień miesiąca:

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

To zwraca ostatni dzień roku:

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

To zwraca ostatni dzień tygodnia dla tygodnia, który zaczyna się w niedzielę:

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

Zwraca ostatni dzień tygodnia dla tygodnia, który zaczyna się w poniedziałek:

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)

Opis

Konwertuje łańcuchową reprezentację datetime na obiektDATETIME.

format_string zawiera elementy formatu, które określają sposób formatowania datetime_string. Każdy element wdatetime_string musi mieć odpowiadający mu element w format_string. Położenie każdego elementu w format_string musi odpowiadać położeniu każdego elementu w datetime_string.

Łańcuch formatu w pełni obsługuje większość elementów formatu, z wyjątkiem%Q, %a, %A,%g, %G, %j, %P, %u, %U, %V, %w i %W.

PARSE_DATETIME parsuje string zgodnie z następującymi regułami:

• Pola nieokreślone. Każde nieokreślone pole jest inicjalizowane z1970-01-01 00:00:00.0. Na przykład, jeśli rok jest nieokreślony, to domyślnie 1970.
• Nazwy niewrażliwe na wielkość liter. W nazwach, takich jak Monday i February, nie jest rozróżniana wielkość liter.
• Białe spacje. Jedna lub więcej kolejnych białych spacji w łańcuchu formatu odpowiada zeru lub więcej kolejnych białych spacji w łańcuchuDATETIME. Wiodące i końcowe białe spacje w łańcuchu DATETIME są zawsze dozwolone, nawet jeśli nie ma ich w łańcuchu formatu.
• Pierwszeństwo formatu. Gdy dwa lub więcej elementów formatu ma nakładające się informacje, ostatni z nich generalnie zastępuje wszystkie wcześniejsze, z pewnymi wyjątkami. Na przykład, zarówno %F jak i %Y wpływają na rok, więc wcześniejszy element jest nadrzędny w stosunku do późniejszego. Zobacz opisy %s, %C i %y w sekcjiSupported Format Elements For DATETIME.

Return Data Type

DATETIME

Przykłady

Następujące przykłady parsują STRING literał jakoDATETIME.

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

Następujący przykład przetwarza STRING literał zawierający datę w formacie języka naturalnego jakoDATETIME.

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

Obsługiwane elementy formatu dla DATETIME

Jeśli nie zaznaczono inaczej, DATETIMEfunkcje, które używają łańcuchów formatu, obsługują następujące elementy: