Datetime-funktiot Standard SQL:ssä

BigQuery tukee seuraavia DATETIME-funktioita.

Kaikki tulosteet muotoillaan automaattisesti ISO 8601 -standardin mukaisesti, jolloin päivämäärä ja kellonaika erotetaan toisistaan a-kirjaimellaT.

CURRENT_DATETIME

CURRENT_DATETIME()

Kuvaus

Palauttaa nykyisen ajan DATETIME-oliona.

Tämä funktio tukee valinnaista timezone-parametria.Katso Aikavyöhykemääritykset saadaksesi tietoa aikavyöhykkeen määrittämisestä.

Palauta tietotyyppi

DATETIME

Esimerkki

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

DATETIME

Kuvaus

1. Konstruoi DATETIME-olion, jossa käytetään INT64-arvoja, jotka edustavat vuosilukua, kuukauttamme, päivämäärää, työtuntia, -minuuttia ja -hetkeä.
2. Konstruoi DATETIME-olion käyttäen DATE-oliota ja valinnaista TIME-oliota.
3. Konstruoi DATETIME-olion käyttäen TIMESTAMP-oliota. Se tukee valinnaista parametria aikavyöhykkeen määrittämiseksi. Jos aikavyöhykettä ei määritetä, käytetään oletusaikavyöhykettä UTC.

Palauta tietotyyppi

DATETIME

Esimerkki

EXTRACT

EXTRACT(part FROM datetime_expression)

Kuvaus

Palauttaa arvon, joka vastaa määriteltyä part annetusta datetime_expression:stä.

Sallitut part-arvot ovat:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAYOFWEEK
• DAYOFWEEK
• DAY
• HOUR
• DAY
• : Palauttaa päivämäärän viikon numeron alueella . Viikot alkavat sunnuntaista, ja vuoden ensimmäistä sunnuntaita edeltävät päivämäärät ovat viikolla 0.
• WEEK(<WEEKDAY>): Palauttaa viikon numeron datetime_expression alueella . Viikot alkavat WEEKDAY.datetimevuoden ensimmäistä WEEKDAY edeltävät päivät ovat viikolla 0. Validvalues for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY,THURSDAY, FRIDAY, and SATURDAY.
• ISOWEEK: Palauttaa datetime_expression:n ISO 8601 -viikkonumeron. ISOWEEK:t alkavat maanantaina. Palautusarvot ovat välillä . Kunkin ISO-vuoden ensimmäinen ISOWEEK alkaa gregoriaanisen kalenterivuoden ensimmäistä torstaita edeltävänä maanantaina.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Palauttaa ISO 8601 -viikkonumerointivuoden, joka on gregoriaaninen kalenterivuosi, joka sisältää sen viikon torstain, johon date_expression kuuluu.
• DATE
• TIME

Palautetut arvot katkaisevat alemman järjestyksen aikajaksot. Esimerkiksi, kun uutetaan sekuntia, EXTRACT typistää millisekunti- ja mikrosekuntiarvot.

Palauta tietotyyppi

INT64, paitsi seuraavissa tapauksissa:

• Jos part on DATE, palauttaa DATE-olion.
• Jos part on TIME, palauttaa TIME-olion.

Esimerkkejä

Seuraavassa esimerkissä EXTRACT palauttaa arvon, joka vastaa HOURaikaosaa.

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

Seuraavassa esimerkissä EXTRACT palauttaa arvoja, jotka vastaavat eri aikaosia sarakkeesta datetimes.

Seuraavassa esimerkissä datetime_expression osuu sunnuntaille. EXTRACTlaskee ensimmäisen sarakkeen käyttäen viikkoja, jotka alkavat sunnuntaina, ja se laskee toisen sarakkeen käyttäen viikkoja, jotka alkavat maanantaina.

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

Kuvaus

Lisää int64_expression-yksikön part yksiköt DATETIME-olioon.

DATETIME_ADD tukee seuraavia part-arvoja:

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

Erityiskäsittelyä vaativat KUUKAUSI-, NELJÄNNES- ja VUOSI-osiot, kun päivämäärän päivämäärä on kuun viimeisenä päivänä (tai lähellä sitä). Jos tuloksena syntyvässä kuukaudessa on vähemmän päiviä kuin alkuperäisen DATETIMEn päivä, tuloksena oleva päivä on uuden kuukauden viimeinen päivä.

Palauta tietotyyppi

DATETIME

Esimerkki

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

Kuvaus

Subtrakoi DATETIME:stä int64_expression part yksikön.

DATETIME_SUB tukee seuraavia part:n arvoja:

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

Erityiskäsittelyä vaativat MONTH, QUARTER ja YEAR-osat, kun päiväys on kuukauden viimeisenä päivänä (tai lähellä sitä). Jos tuloksena syntyvässä kuukaudessa on vähemmän päiviä kuin alkuperäisen DATETIME:n päivässä, tuloksena oleva päivä on uuden kuukauden viimeinen päivä.

Palauta tietotyyppi

DATETIME

Esimerkki

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

Kuvaus

Palauttaa kahdenDATETIME kohteen väliin jäävien kokonaisten määritettyjen part aikavälien lukumäärän part (datetime_expression_a – datetime_expression_b).Jos ensimmäinen DATETIME on aikaisempi kuin toinen,tulos on negatiivinen. Heittää virheen, jos laskenta ylittää tulostyypin, esimerkiksi jos kahden DATETIME-kohteen välinen ero mikrosekunneissa ylittäisi INT64-arvon.

DATETIME_DIFF tukee seuraavia part-arvoja:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• DAY
• WEEK: Tämä päivämäärän osa alkaa sunnuntaina.
• WEEK(<WEEKDAY>): Tämä päivämääräosa alkaa WEEKDAY. Kelvollisia arvoja WEEKDAY:lle ovat SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY ja SATURDAY.
• ISOWEEK: Käyttää ISO 8601 -viikkorajoja. ISO-viikot alkavat maanantaina.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Käyttää ISO 8601:n mukaista viikkonumeroinnin vuosirajaa. ISO-vuosiraja on sen ensimmäisen viikon maanantai, jonka torstai kuuluu vastaavaan gregoriaaniseen kalenterivuoteen.

Palauta tietotyyppi

INT64

Esimerkki

Oheinen esimerkki näyttää DATETIME_DIFF:n tuloksen kahdelle DATETIME:lle, joiden välissä on 24 tuntia. DATETIME_DIFF osalla WEEK antaa tulokseksi 1, koska DATETIME_DIFF laskee osarajojen lukumäärän tällä DATETIMEs-välillä. Jokainen WEEK alkaa sunnuntaina, joten lauantain 2017-10-14 00:00:00 ja sunnuntain 2017-10-15 00:00:00 välillä on yksi osan raja.

Seuraavassa esimerkissä näytetään DATETIME_DIFF:n tulos kahdelle eri vuosiluvulla olevalle päivämäärälle. DATETIME_DIFF päivämäärän osalla YEAR antaa tulokseksi 3, koska se laskee gregoriaanisen kalenterivuoden rajojen lukumäärän kahden DATETIME:n välillä. DATETIME_DIFF, jonka päivämääräosa on ISOYEAR, palauttaa 2, koska toinen DATETIME kuuluu ISO-vuoteen 2015. Vuoden 2015kalenterivuoden ensimmäinen torstai oli 2015-01-01, joten ISO-vuosi 2015 alkaa sitä edeltävänä maanantaina, 2014-12-29.

Seuraavassa esimerkissä näytetään DATETIME_DIFF:n tulos kahden päivän välein. Ensimmäinen päivä osuu maanantaille ja toinen päivä osuu sunnuntaille. DATETIME_DIFF päivämääräosalla WEEK antaa tulokseksi 0, koska tämä aikaosa käyttää sunnuntaina alkavia viikkoja. DATETIME_DIFF päivämäärän WEEK(MONDAY) kanssa palauttaa arvon 1. DATETIME_DIFF päivämääräosallaISOWEEK palauttaa myös 1, koska ISO-viikot alkavat maanantaina.

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Kuvaus

Pätkistää DATETIME-olion rakeisuuteen part.

DATETIME_TRUNC tukee seuraavia arvoja part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): Katkaisee datetime_expression edeltävään viikkorajaan, jossa viikot alkavat WEEKDAY. WEEKDAY:n kelvolliset arvot ovatSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY jaSATURDAY.
• ISOWEEK: Katkaisee datetime_expression:n edeltäväänISO 8601-viikkorajan rajaan. ISOWEEK alkaa maanantaina. Kunkin ISO-vuoden ensimmäinen ISOWEEK sisältää vastaavan gregoriaanisen kalenterivuoden ensimmäisen torstain. Kaikki tätä aikaisemmat date_expression katkaistaan edeltävään maanantaihin.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Katkaisee datetime_expression edeltävään ISO 8601-viikkonumerointivuoden rajaan. ISO-vuosiraja on sen ensimmäisen viikon maanantai, jonka torstai kuuluu vastaavaan gregoriaanisen kalenterivuoteen.

Palauta tietotyyppi

DATETIME

Esimerkkejä

Alkuperäinen DATETIME osuu seuraavassa esimerkissä sunnuntaille. Koska part on WEEK(MONDAY), DATE_TRUNC palauttaa edeltävän maanantain DATETIME.

Seuraavassa esimerkissä alkuperäinen datetime_expression on gregoriaanisen kalenterin vuonna 2015. Kuitenkin DATETIME_TRUNC päivämäärän ISOYEAR kanssa katkaisee datetime_expression:n ISO-vuoden alkuun, eiGregoriaanisen kalenterivuoden alkuun. Kalenterivuoden 2015 ensimmäinen torstai oli2015-01-01, joten ISO-vuosi 2015 alkaa sitä edeltävänä maanantaina 2014-12-29.Näin ollen datetime_expression2015-06-15 00:00:00:00 edeltävä ISO-vuoden raja on 2014-12-29.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Kuvaus

Muotoilee DATETIME-olion määritellyn format_string mukaisesti. KatsoTuetut muotoelementit DATETIME:lleLuettelo muotoelementeistä, joita tämä funktio tukee.

Palauta tietotyyppi

STRING

Esimerkkejä

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)

Kuvaus

Palauttaa viimeisimmän päivän päivämäärän sisältävästä datetime-ilmauksesta.Tätä käytetään yleisesti kuukauden viimeisen päivän palauttamiseen.

Voit määrittää valinnaisesti päivämäärän osan, jonka viimeinen päivä palautetaan.Jos tätä parametria ei käytetä, oletusarvo on MONTH.LAST_DAY tukee seuraavia arvoja date_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Vastaa 7 DAYs.
• WEEK(<WEEKDAY>). <WEEKDAY> edustaa alkavaa viikonpäivää.Kelvollisia arvoja ovat SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY ja SATURDAY.
• ISOWEEK. Käyttää ISO 8601 -viikkorajoja. ISO-viikot alkavat maanantaina.
• ISOYEAR. Käyttää ISO 8601 -viikkonumeroinnin vuosirajaa.ISO-vuosiraja on sen ensimmäisen viikon maanantai, jonka torstai kuuluu vastaavaan gregoriaaniseen kalenterivuoteen.

Palauta tietotyyppi

DATE

Esimerkki

Nämä molemmat palauttavat kuukauden viimeisen päivän:

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

Tämä palauttavat vuoden viimeisen päivän:

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

Tämä palauttavat sunnuntaina alkavan viikon viimeisen päivän:

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

Tämä palauttaa maanantaina alkavan viikon viimeisen viikonpäivän:

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)

Kuvaus

Muuntaa datetime-ajan merkkijonoesityksenDATETIME-olioksi.

format_string sisältää format-elementit, jotka määrittelevät, miten datetime_string muotoillaan. Jokaisella elementillä datetime_string:ssä on oltava vastaava elementti format_string:ssä. Jokaisen elementin sijainnin format_string:ssä on vastattava jokaisen elementin sijaintia datetime_string:ssä.

Formaattijono tukee täysin useimpia formaattielementtejä, paitsi %Q, %a, %A,%g, %G, %j, %P, %u, %U, %V, %w ja %W.

PARSE_DATETIME jäsentää string seuraavien sääntöjen mukaisesti:

• Määrittelemättömät kentät. Mikä tahansa määrittelemätön kenttä alustetaan 1970-01-01 00:00:00.0:stä. Jos esimerkiksi vuosi on määrittelemätön, sen oletusarvo on 1970.
• Isot ja pienet nimet. Nimet, kuten Monday ja February,eivät erota isoja ja pieniä kirjaimia.
• Whitespace. Yksi tai useampi peräkkäinen valkoinen välilyönti muotoilujonossa vastaa nollaa tai useampaa peräkkäistä valkoista välilyöntiäDATETIME-merkkijonossa. Johtavat ja perässä olevat valkoiset välilyönnit DATETIME-merkkijonossa ovat aina sallittuja, vaikka ne eivät olisikaan muotoilujonossa.
• Muotoilun etusija. Kun kahdella tai useammalla formaattielementillä on päällekkäistä tietoa, viimeisin elementti ohittaa yleensä kaikki aikaisemmat, joitakin poikkeuksia lukuun ottamatta. Esimerkiksi sekä %F että %Y vaikuttavat vuosilukuun, joten aikaisempi elementti ohittaa myöhemmän. Katso %s:n, %C:n ja %y:n kuvaukset kohdasta DATETIME:n tuetut muotoelementit.

Palauta tietotyyppi

DATETIME

Esimerkkejä

Seuraavat esimerkit parsivat STRING-literaalinDATETIME:ksi.

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

Seuraava esimerkki jäsentää STRING-literaalin, joka sisältää päivämäärän luonnollisessa kielimuodossa, DATETIME:ksi.

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

Tuetut formaattielementit DATETIME:lle

Jollei toisin mainita, DATETIME-funktiot, jotka käyttävät formaatti-merkkijonoja, tukevat seuraavia elementtejä:

.