Oracle Date Functions: Täydellinen opas (2024)

Oraclessa on monia erilaisia ​​päivämäärä- ja aikatoimintoja, ja ne toimivat eri tavalla kuin muut tietokannat. Opi, mitä ne ovat, ja katso hyödyllisiä kysymyksiä tästä artikkelista.

Oraclen päivämääräfunktiot ovat mitä tahansa toimintoja, jotka toimivat päivämäärä- ja/tai aikaarvojen kanssa. Ne ottavat usein päivämäärät syötteenä ja palauttavat monia erilaisia ​​tulosteita.

Selitän, mitä kukin Oraclen päivämääräfunktio on, ja näytän esimerkin.

Aloitetaan tarkastelemalla Oraclen eri päivämäärään liittyviä tietotyyppejä.

Päivämäärä- ja aikatietotyypit Oraclessa

Niitä on muutamaeri tietotyyppejäOraclessa, joka tallentaa päivämääräarvot. He ovat:

• DATE: Oraclen "tavallinen" päivämääräarvo. Se tallentaa vuoden, kuukauden, päivän sekä tunnin, minuutin ja sekunnin. Kyllä, vaikka sitä kutsutaan "päivämääräksi", se tallentaa aikakomponentin. Tämä on hyvä muistaa.
• AIKALEIMA: Tämä tietotyyppi tallentaa vuoden, kuukauden, päivän, tunnin, minuutin, sekunnin sekä sekuntien murto-osat.
• TIMESTAMP WITH TIME ZONE: Tämä tietotyyppi on sama kuin TIMESTAMP, mutta se tallentaa aikavyöhykkeen sen mukana.
• TIMESTAMP WITH LOCAL TIME ZONE: Tämä tietotyyppi on samanlainen kuin TIMESTAMP WITH TIME ZONE, mutta tallennettu aikavyöhyke on tietokannan aikavyöhyke.
• AIKAVÄLI VUODESTA KUUKAUSIIN: Tämä tietotyyppi tallentaa "ajanjakson" (eikä "ajankohdan" kuten muut tietotyypit) ja edustaa ajanjaksoa kuukausista vuosiin.
• AIKAVÄLI PÄIVÄSTÄ SEKUNTIIN: Tämä tietotyyppi tallentaa myös ajanjakson, mutta tallentaa arvon, joka kaappaa sekunteja aina päiviin asti.

Joten nyt olemme tarkastelleet eri tietotyyppejä, katsotaanpa erilaisia ​​Oracledate-toimintoja.

SYSDATE-toiminto

Oracle SYSDATE -toiminnon avulla voit helposti tulostaa nykyisen päivämäärän. Se näyttää tietokantapalvelimen päivämäärän ja kellonajan.

Voit käyttää sitä kirjoittamalla sanan SYSDATE. Sillä ei ole parametreja, mikä tarkoittaa, että et tarvitse sulkuja sen jälkeen.

Esimerkki SYSDATE-funktiosta on:

SELECT SYSDATEFROM dual;

Tulos:

10.9.22

Olen käyttänyt DUAL-taulukkoa, koska Oraclen on valittava taulukosta valintakyselyssä, ja DUAL-taulukko on hyödyllinen tähän (lue lisääkaksoispöytä täällä).

Kuten näet, nykyinen päivämäärä näytetään. Se näyttää kuitenkin vain päivämäärän. Tämä johtuu siitä, että päivämäärien SELECT-kyselyiden tulostusmuoto on tällä hetkellä asetettu muotoon PP/KK/VVVV.

SYSDATE-funktio palauttaa kuitenkin myös aikakomponentin. Voimme nähdä tämän, jos muutamme istunnon päivämäärän muotoa tai ympäröimme SYSDATE funktiolla TO_CHAR.

ALTER SESSIONSET NLS_DATE_FORMAT = 'VVVV-KK-PP HH24:MI:SS';

Suoritetaan nyt SELECT-lause uudelleen SYSDATE-funktiolla.

SELECT SYSDATEFROM dual;

Tulos:

2022-09-10 08:54:19

Kuten näet, tulos näyttää nyt päivämäärän ja kellonajan, joka on palautettu SYSDATE:stä. Muutimme istunnon päivämäärämuodon sisällyttämään kellonajan.

Voisimme käyttääTO_CHAR-toimintomuuttaaksesi tapaa, jolla tämä toiminto näytetään:

SELECTTO_CHAR(SYSDATE, 'PP/MM/YYYY HH:MI:SS') AS sysdate_timeFROM dual;

Tulos:

10/09/2022 08:54:19

Voit nähdä, että tulos sisältää nyt päivämäärän ja kellonajan.

CURRENT_DATE

CURRENT_DATE-funktio on samanlainen kuin SYSDATE-funktio, mutta se näyttää sinullenykyinen päivämäärä ja aika istunnon aikavyöhykkeellä.

Tämä on aikavyöhyke, jolla istuntosi on, tai aikavyöhyke, jolla olet kirjautuessasi järjestelmään. Tämä voi joskus poiketa tietokannan aikavyöhykkeestä.

Voit käyttää tätä toimintoa lisäämällä kyselyyn sana CURRENT_DATE. Sulkuja ei tarvita, koska parametreja ei ole.

SELECT CURRENT_DATEFROM dual;

Tulos:

10.9.22

Tämä näyttää käyttäjäistunnon nykyisen päivämäärän.

Aivan kuten SYSDATE-toiminnon kanssa, tämä toimintopalauttaa datatyypin DATE, joka itse asiassa sisältää päivämäärän ja kellonajan. Jos haluat näyttää tämän arvon aikakomponentin, käytä joko TO_CHAR-funktiota tai muuta istuntoa niin, että se sisältää ajan muodon.

ALTER SESSION SET NLS_DATE_FORMAT = 'VVVV-KK-PP HH24:MI:SS';

Toinen tapa on muuttaa muotoa TO_CHAR-funktiolla.

SELECTTO_CHAR(CURRENT_DATE, 'PP/MM/YYYY HH:MI:SS')FROM dual;

Tulos:

10/09/2022 08:54:19

Voit nähdä, että tulos sisältää nyt päivämäärän ja kellonajan.

Oracle CURRENT_DATE vs. SYSDATE

Suurin ero CURRENT_DATE ja SYSDATE välillä on:

• CURRENT_DATE palauttaa päivämäärän sinunistuntoaikavyöhyke (oma aikavyöhyke).
• SYSDATE palauttaa päivämäärän alkaentietokantaaikavyöhyke.

Jos olet Yhdysvaltojen itärannikolla ja tietokantasi on länsirannikolla, nämä toiminnot näyttävät eri arvoja. Jos olet Australiassa ja tietokanta on Lontoossa, ne näyttävät eri arvoja.

Saattavat jopaKatsosama, mutta itse asiassa erilainen.

Tämä johtuu siitä, että oletuslähtö on DD-MON-YY, joka piilottaa ajan.

Tämä on oletustulos, olettaen, että olen Melbournessa (UTC +10) ja tietokanta on Perthissä (UTC +8).

SELECT CURRENT_DATE, SYSDATEFROM kaksi;

Tulos:

Nämä arvot näyttävät samalta, eikö?

Jos nyt sisällytän aikakomponentin käyttämälläTO_CHAR, tulokseni osoittaa, että nämä kaksi arvoa ovat erilaisia.

SELECTTO_CHAR(CURRENT_DATE, 'DD-MON-YY HH:MI:SS') AS CDATE,TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS') AS SDATEFROM dual;

Tulos:

Kuinka saan Oraclen CURRENT_DATE – 1 päivä?

Jos haluat löytää tietueita, jotka ovat pienempiä, suurempia tai yhtä suuria kuin edellisenä päivänä (tai mitä tahansa vertailua), voit tehdä tämän Oraclen CURRENT_DATE -funktiolla.

Sinun on vain käytettäväTRUNKyhtä hyvin.

SELECTCURRENT_DATE,TRUNC(CURRENT_DATE) AS trunc_today,TRUNC(CURRENT_DATE)-1 AS trunc_yesterdayFROM dual;

Tulos:

Voit käyttää tätä TRUNC(CURRENT_DATE)-1-logiikkaa muissa SQL-kyselyissäsi:

--Etsi työntekijöitä, jotka palkattiin eilen tai myöhemmin.VALITSE nimiFROM työntekijäWHERE palkkauspäivä >=TRUNC(CURRENT_DATE)-1

SYSTIMESTAMP

SYSTIMESTAMP-funktio palauttaatietokannan päivämäärä ja aika.

Tärkeää muistaa, että se palauttaa tietokannan ajan, ei paikallista aikaa.

Joten jos käytät tietokantaa eri aikavyöhykkeellä, se palauttaa sen paikan ajan ja aikavyöhykkeen, johon tietokanta on tallennettu, ei paikallista aikavyöhykettäsi.

Jos tietokantasi on esimerkiksi Lontoossa, mutta olet New Yorkissa, SYSTIMESTAMP palauttaa ajan, joka on 5 tuntia aikaasi edellä.

Se on samanlainen kuin SYSDATE-toiminto, vaikka se onkinpalauttaa AIKALEIMA AIKAVYÖHKEELLÄDATE-tietotyypin sijaan.

Tämä tarkoittaa, että se sisältää päivämäärän, kellonajan ja sekuntien murto-osat.

SELECT SYSTIMESTAMPFROM dual;

Tulos:

10/SEP/22 08:57:17.067000000 +10:00

Nyt, koska se on AIKALEIMA AIKAVYÖHKEELLÄ, se näyttää ajan oletuksena, toisin kuin PÄIVÄMÄÄRÄ-tietotyyppi.

Näet ulostulon tässä näyttää päivämäärän, kellonajan, sekuntien murto-osat ja aikavyöhykkeen. Sinun ei tarvitse alustaa sitä nähdäksesi täydellisen tulosteen.

Kuinka voin lisätä SYSTIMESTAMPilla?

Voit lisätä SYSTIMESTAMP:n palauttaman arvon muutamalla tavalla.

Ensinnäkin voit varmistaa, että sarake, johon lisäät, on tyyppiä TIMESTAMP WITH TIME ZONE.

Vaihtoehtoisesti, jos se ei ole, voit muuntaa arvon VARCHAR2-tietotyypiksi TO_CHAR-funktiolla ja lisätä sen.

Yleensä ei kuitenkaan ole hyvä idea lisätä päivämääriä merkkikenttiin. Suosittelen lisäämään arvon muodossa TIMESTAMP WITH TIME ZONE.

Voinko löytää SYSTIMESTAMP ilman aikavyöhykettä?

Kyllä sinä voit.

Voit käyttää SYSDATE-toimintoa saadaksesi vain päivämäärän ja kellonajan, mutta siinä ei ole sekuntien murto-osia.

Voinko käyttää TO_CHAR:a SYSTIMESTAMP:n kanssa?

Kyllä sinä voit. Katso lisätietoja alla olevista esimerkeistä.

Voit sisällyttää SYSTIMESTAMP-funktion kansioon TO_CHAR ja määrittää tulostettavien arvojen muodon.

Esimerkki 1

Tämä on funktiot SYSDATE ja SYSTIMESTAMP vierekkäin.

SELECTSYSDATE,SYSTIMESTAMPFROM kaksi;

Tulos:

Esimerkki 2

Tämä esimerkki käyttää TO_CHAR-funktiota järjestelmässä SYSTIMESTAMP.

SELECTSYSTIMESTAMP,TO_CHAR(SYSTIMESTAMP, 'dd ma yyyy') AS text_outputFROM dual;

Tulos:

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP-funktio palauttaapäivämäärä ja aika istunnon aikavyöhykkeellä, tai mistä olet kirjautunut sisään.

Se on samanlainen kuin CURRENT_DATE-funktio, koska se palauttaa istunnon päivämäärän ja kellonajan tietokannan sijaan. Se on myös samanlainen kuin SYSTIMESTAMP-funktio, koska se palauttaa aikaleiman päivämäärän sijaan.

Thepalautustietotyyppi on AIKALEIMA AIKAVYÖHKEELLÄ, joka sisältää päivämäärän, kellonajan, sekuntien murto-osat ja aikavyöhykkeen.

CURRENT_TIMESTAMP sisältää yhden valinnaisen parametrin: palautettavien sekuntien tarkkuuden tai murto-osien lukumäärän. Jos se jätetään pois, se käyttää 6 paikkaa.

CURRENT_TIMESTAMP ([tarkkuus])

Esimerkki tästä toiminnosta on:

SELECT CURRENT_TIMESTAMPFROM dual;

Tulos:

10/SEP/22 08:57:49.020000000 AM AUSTRALIA/MELBOURNE

Voit nähdä, että nykyinen päivämäärä ja aika näkyvät tässä yhdessä aikavyöhykkeen kanssa.

Voinko vähentää tunnit ajankohdasta CURRENT_TIMESTAMP?

Kyllä sinä voit. Paras tapa tehdä tämä on käyttää intervallitietotyyppiä, koska säilytät alkuperäisen tietotyyppisi.

Jos esimerkiksi haluaisin vähentää 4 tuntia nykyisestä ajasta, tekisin näin:

SELECTCURRENT_TIMESTAMP, CURRENT_TIMESTAMP - INTERVAL '4' HOURFROM dual;

Tulos:

LOCALTIME LEIMA

Viimeinen funktio, joka saa nykyisen päivämäärän, on LOCALTIMESTAMP-funktio.

Tämä toiminto saaistunnon nykyinen päivämäärä ja aika.

Se on samanlainen kuin CURRENT_TIMESTAMP-funktio, muttaLOCALTIMESTAMP palauttaa TIMESTAMP-arvonja CURRENT_TIMESTAMP palauttaa AIKALEIMA AIKAVYÖHYKEARVOLLA.

Tämä tarkoittaa, että LOCALTIMESTAMP palauttaa vain päivämäärän ja kellonajan, ei aikavyöhykettä.

LOCALTIMESTAMP-toiminto näyttää tältä:

SELECT LOCALTIMESTAMPFROM dual;

Tulos:

10/SEP/22 08:58:25.614000000 AM

Voit nähdä, että tulos näyttää päivämäärän, kellonajan ja sekuntien murto-osan.

LOCALTIMESTAMP-funktion parametrit ovat:

• timestamp_precision (pakollinen): Tämä määrittää palautusarvon sekuntien numeroiden määrän, joka tunnetaan myös "sekunnin murto-osona".

ADD_MONTHS

ADD_MONTHS-funktion avulla voit syöttää päivämäärän arvon ja kuukausien määrän sekä palauttaa toisen päivämääräarvon. Palautettu arvo on syötetyn päivämäärän arvo plus antamiesi kuukausien määrä.

Joten jos aloitat 10. tammikuuta 2022 ja lisäät siihen 3 kuukautta, funktio palauttaa 10. huhtikuuta 2022.

Syntaksi on:

ADD_MONTHS (syöttöpäivämäärä, kuukausien_määrä)

Esimerkki tästä toiminnosta on:

SELECTSYSDATE,ADD_MONTHS(SYSDATE, 5) AS new_dateFROM dual;

Tämä kysely näyttää SYSDATE sekä SYSDATE 5 kuukauden lisättynä. Näet, että sillä on sama päivä, mutta se on 5 kuukautta tulevaisuudessa.

Voit myös antaa negatiivisia arvoja:

SELECTSYSDATE,ADD_MONTHS(SYSDATE, -12) AS new_dateFROM dual;

Tämä näyttää päivämäärän 12 kuukauden ajalta.

Lisätietoja ADD_MONTHS-funktion käytöstä, mukaan lukien "mitä jos" -kysymykset, saat tästä artikkelista:Oracle ADD_MONTHS -toiminto esimerkkien kanssa.

VIIMEINEN PÄIVÄ

LAST_DAY-funktio palauttaa määritetyn päivämääräarvon kuukauden viimeisen päivän.

Annat päivämäärän, ja toinen päivämäärä palautetaan, joka on kuukauden viimeinen päivä.

Syntaksi on:

LAST_DAY (syöttöpäivämäärä)

Katsotaanpa esimerkkiä:

SELECTSYSDATE,LAST_DAY(SYSDATE) AS viimeinen_kuukauden_FROM dual;

Tässä näet, että olen näyttänyt tämän päivän päivämäärän käyttämällä SYSDATE ja myös tämän kuun viimeisen päivän käyttämällä LAST_DAY-toimintoa.

Entä jos haluaisin määrittää tietyn päivämäärän?

Voit tehdä sen myös syöttämällä päivämäärän tietokannan oletusmuodossa (yleensä PP-MON-YY) tai käyttämällä TO_DATE-funktiota.

SELECTLAST_DAY('03-OCT-2022')FROM dual;

Tulos:

31.10.2022

Lisätietoja LAST_DAY-funktiosta, kuten seuraavan kuukauden ensimmäisen päivän palauttamisesta (ja muita esimerkkejä), lue artikkelini:Oracle LAST_DAY -funktio ja esimerkkejä.

SEURAAVA PÄIVÄ

NEXT_DAY-funktio palauttaa seuraavan määritetyn viikonpäivän päivämäärän, joka tulee määritetyn arvon jälkeen.

Se on kätevä työskennellä minkä tahansa liiketoimintalogiikan kanssa, kun tarvitset arkipäiviä.

Se vaatii kaksi parametria:

NEXT_DAY (syöttöpäivämäärä, viikonpäivä)

Syötä päivämäärä on päivämäärä, josta alkaa, ja viikonpäivä on etsimäsi päivän nimi.

Esimerkiksi, jos SYSDATE on käynnissä tämän päivän päivänä, se näyttää 15. syyskuuta 2022, ja se on tiistai.

Voit etsiä seuraavan torstain, joka näkyy tämän päivän jälkeen, käyttämällä tätä toimintoa:

SELECTNEXT_DAY(SYSDATE, 'TORSTAI')FROM kaksi;

Tulos:

15. syyskuuta 22

Voit nähdä, että se näyttää päivämäärän 15. syyskuuta, joka on seuraava torstai, joka tulee mainitsemani päivämäärän jälkeen.

Jos haluan löytää seuraavan maanantain, muutan toimintoa hieman:

SELECTNEXT_DAY(SYSDATE, 'MAANANTAI')FROM dual;

Tulos:

12. syyskuuta 22

Näet, että se näyttää päivämäärän 12. syyskuuta, joka on ensi maanantai. Se ei näy edellisenä päivänä, vaikka se on lähempänä. Se menee aina eteenpäin.

Saat lisätietoja NEXT_DAY-toiminnosta lukemalla artikkelini täältä:Oracle NEXT_DAY -funktio ja esimerkkejä.

MONTHS_BETWEEN

MONTHS_BETWEEN-funktion avulla voit löytää kahden määritetyn päivämäärän välisten kuukausien määrän.

Määrität parametreiksi kaksi päivämäärää ja palautetaan numero:

MONTHS_BETWEEN (päivämäärä1, päivämäärä2)

Päiväys1 on yleensä myöhempi päivämäärä.

Jos päivämäärä1 on suurempi kuin päivämäärä2, arvo on positiivinen. Muuten se on negatiivinen.

Palautettu arvo voi olla kokonaisluku tai desimaaliluku. Joten se palauttaa osittaisen kuukauden arvon.

Katsotaanpa esimerkkiä:

SELECTMONTHS_BETWEEN('12-TOUKOKUU-2022', '10-FEB-2022')FROM dual;

Tulos:

3,064516129

Palautettu arvo on 3,06, koska näiden kahden päivämäärän välillä on noin 3,06 kuukautta.

PYÖRISTÄÄ

ROUND-toiminnon avulla voit pyöristää päivämäärän arvon määrittämääsi muotoon.

Tätä toimintoa käytetään usein numeroiden kanssa, mutta sitä voidaan käyttää myös päivämäärien kanssa.

Jos käytät sitä päivämääräarvon kanssa, voit määrittää DATE- tai TIMESTAMP-arvon. Voit määrittää minkä tahansa muotomaskin, mutta oletusarvo on lähin päivä, ja se palautetaan DATE.

Syntaksi on:

ROUND (syöttöpäivämäärä, pyöreä_määrä)

Jos haluaisin pyöristää päivämäärän lähimpään kuukauteen, käyttäisin jotain tällaista:

SELECTROUND(SYSDATE, 'MM')FROM dual;

Tulos:

1. syyskuuta 22

Tämä näyttää arvon 1 Syyskuu, koska määritetty päivämäärä (SYSDATE tai 10. syyskuuta) on pyöristetty eteenpäin tähän päivämäärään.

Jos määritämme toisen päivämäärän ja syötteen:

SELECTROUND(SYSDATE, 'YYYY')FROM dual;

Tulos:

1.1.23

Näemme, että päivämäärä on 1.1.2023. Tämä johtuu siitä, että se on pyöristetty eteenpäin lähimpään vuoteen, ja tässä tapauksessa se on vuoden 2023 alku.

Jos haluat lisätietoja ROUND-toiminnosta, lue artikkelini:SQL ROUND -funktio esimerkkien kanssa.

TRUNK

TRUNC-toiminto, kuten pyöreä toiminto, toimii sekä numeroiden että päivämäärien kanssa.

Se katkaisee tai poistaa osan päivämäärästä määrittämääsi muotoon.

Syntaksi on:

TRUNC (syöttöpäivämäärä, muotomask)

Jos et määritä muotomaskia, funktio katkaisee arvon lähimpään päivään. Tämä on hyödyllistä, jos haluat poistaa päivämääräarvon aikaosan.

Jos haluat esimerkiksi näyttää vain tämän päivän päivämäärän päivämääräosan:

SELECTTRUNC(SYSDATE)FROM dual;

Tulos:

10.9.22

Tämä näyttää vain tämän päivän päivämääräosan.

Tai voit näyttää vain VUODEN:

SELECTTRUNC(SYSDATE, 'YYYY')FROM dual;

Tulos:

1.1.22

Näet päivämäärän, joka vastaa kuluvan vuoden ensimmäistä päivää.

Jos haluat lisätietoja TRUNC-toiminnosta, lue artikkelini täältä:Oracle TRUNC -funktio esimerkkien kanssa.

OTTAA TALTEEN

Oraclen EXTRACT-toiminto poimii tietyn osan päivämäärästä päivämäärän tai välin arvosta.

Tämä tarkoittaa, että se voi saada esimerkiksi kuukauden tai vuoden DATE-arvosta. Mielestäni se on helpompaa kuin muunnosfunktion, kuten TO_CHAR, käyttäminen.

EXTRACT-toiminto näyttää tältä:

EXTRACT (date_component FROM lauseke)

Date_component on avainsana, joka edustaa poimittavaa päivämäärän osaa, kuten MONTH, DAY, YEAR tai HOUR.

Lauseke on arvo tai sarake, joka on päivämäärän tai aikavälin tietotyyppi.

Esimerkki tästä toiminnosta on:

SELECTSYSDATE,EXTRACT(MONTH FROM SYSDATE) AS otteen_kuukausiFROM dual;

Tämä näyttää SYSDATE kuukauden osan.

Lisätietoja EXTRACT-toiminnosta on artikkelissani täällä:Oracle EXTRACT -funktio ja esimerkkejä.

TÄHÄN MENNESSÄ

TO_DATE-funktion avulla voit muuntaa merkin arvon päivämääräarvoksi. Se muuntaa minkä tahansa merkin tietotyypin (CHAR, VARCHAR2, NCHAR tai NVARCHAR2) DATE-tyypiksi.

Se on hyödyllinen, jos sinulla on päivämäärä tietyssä muodossa tekstimuodossa tai tekstisarakkeessa ja tarvitset sen PÄIVÄYS-muodossa (esimerkiksi funktiota tai sarakkeeseen lisäämistä varten).

Syntaksi on:

TO_DATE (date_text [, format_mask] [, nls_date_language])

Date_text on päivämäärä, jonka haluat muuntaa, joka on jonkinlainen teksti- tai merkkimuoto. Voit halutessasi määrittää muodon peitteen (joka on muoto, jossa tämä päivämääräarvo on annettu), ja nls_date_langaugea käytetään päivämäärille eri kielissä tai maissa.

Esimerkki tästä toiminnosta on:

SELECTTO_DATE('21-JAN-2022', 'DD-MON-YYYY')FROM dual;

Tulos:

21.1.22

Tämä näyttää määritetyn päivämäärän (21.1.2022) muutettuna päivämäärämuotoon. Se saattaa näyttää samalta IDE:ssäsi, mutta päivämäärät näytetään juuri näin. Arvo aloitettiin merkkiarvona ja muunnetaan päivämääräarvoksi.

Jos päivämäärä on eri muodossa:

SELECTTO_DATE('20220115_142309', 'YYYYMMDD_HH24MISS')FROM dual;

Tulos:

15.1.22

Tämä esimerkki näyttää täysin erilaisen muodon, mutta se voidaan silti muuntaa päivämäärämuotoon. Olemme määrittäneet tässä muotomaskin, joka on muoto, jossa ensimmäinen parametri on.

Lisätietoja TO_DATE-funktiosta on artikkelissani täällä:Oracle TO_DATE -funktio ja esimerkkejä.

UUSI_AIKA

NEW_TIME-funktio muuntaa päivämäärän aikavyöhykkeestä toiseen.

En ole käyttänyt sitä kovin usein, mutta jos sinun on työskenneltävä eri aikavyöhykkeiden kanssa, se voi olla erittäin hyödyllistä.

Syntaksi on:

UUSI_AIKA (syöttöpäivämäärä, syöttöaikavyöhyke, lähtöaikavyöhyke)

Voit käyttää tätä toimintoa määrittämällä input_date ja aikavyöhykkeen, jolla tämä päivämäärä on input_timezone. Määritä sitten aikavyöhyke, johon haluat muuntaa output_timezone -kentässä.

Katsotaanpa esimerkkiä:

SELECTSYSDATE,NEW_TIME(SYSDATE, 'GMT', 'PST') AS converted_timeFROM dual;

Tämä muuntaa nykyisen päivämäärän ja ajan GMT-aikavyöhykkeestä PST-aikavyöhykkeeksi. Huomaa, että näet tässä vain päivämäärät etkä kellonaikoja, koska en ole muuttanut istunnon asetuksia tai käyttänyt toimintoa, kuten TO_CHAR, tulostamaan tulosta.

Selitän alla kaiken TO_CHAR-funktiosta ja sen käyttämisestä päivämäärien muotoiluun, mutta tässä on lyhyt esimerkki:

SELECTTO_CHAR(SYSDATE, 'pp-mm-yy hh:mi:ss AM') AS sysdate_time,TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'PST'), 'dd-mm-yy hh:mi:ss AM') AS newtime_testFROM dual;

Tämä näyttää nyt päivämäärän, joka oli GMT:ssä, mutta muutettuna PST:ksi.

FROM_TZ

Tämä toiminto muuntaa AIKALEIMA-arvon ja määritetyn AIKAVYÖHYKKEEN AIKALEIMAAIKAVYÖHKEELLÄ -arvoksi.

Jos tarvitset arvon, joka on TIMESTAMP WITH TIME ZONE -muodossa, käytä tätä toimintoa.

Syntaksi on:

FROM_TZ (aikaleiman_arvo, aikavyöhykkeen_arvo)

Esimerkki tästä toiminnosta on:

SELECTFROM_TZ(TIMESTAMP '2022-04-19 07:13:50', '-9:00')FROM dual;

Tulos:

19/APR/22 07:13:50.000000000 AM -09:00

Tämä muuntaa määritetyn aikaleiman (joka on päivämäärä ja kellonaika) TIMESTAMP WITH TIME ZONE aikavyöhykkeellä -9 tuntia GMT:stä.

Huomaa TIMESTAMP-avainsana päivämäärän ja kellonajan arvon edessä, mikä tarkoittaa, että arvo on TIMESTAMP-arvo.

SYS_EXTRACT_UTC

SYS_EXTRACT_UTC poimii tai muuntaa määritetyn päivämäärän ja kellonajan UTC-päivämääräksi ja -ajaksi (tunnetaan myös nimellä GMT).

Määrität tietotyypin, jolla on aikavyöhyke, ja palautetaan TIMESTAMP, joka näyttää ajan UTC-ajassa.

Voimme esimerkiksi muuntaa tämän päivämäärän UTC:ksi:

SELECTSYS_EXTRACT_UTC(TIMESTAMP '2022-05-15 19:10:45 +10:00')FROM dual;

Tulos:

15/TOukokuu/22 09:10:45.000000000 AM

Tämä muuntaa määritetyn ajan, joka on +10 UTC, UTC-ajaksi. UTC-aika on 10 tuntia ennen määritettyä aikaa.

Tämä funktio palauttaa eri päivän, jos muunnos johtaa siihen, että tulostuspäivämäärä on eri päivä kuin syöttöpäivämäärä:

SELECTSYS_EXTRACT_UTC(TIMESTAMP '2022-05-15 22:06:12 -7:00')FROM dual;

Tulos:

16/TOUKO/22 05:06:12.000000000 AM

Tämä siirtää aikaa eteenpäin UTC-aikaan, jolloin päivämäärä on eri päivä.

SESSIONTIMEZONE

SESSIONTIMEZONE-funktio palauttaa istunnon aikavyöhykepoikkeaman muodossa [+|-]TZH:TZM tai aikavyöhykkeen alueen nimen.

Tämä voi olla hyödyllistä tietää, varsinkin jos työskentelet paljon päivämäärien kanssa ja työskentelet tietokantojen parissa eri aikavyöhykkeillä.

SESSIONTIMEZONE-funktion syntaksi on melko yksinkertainen.

SESSIONTIMEZONE

On todella vain yksi tapa käyttää SESSIONTIMEZONE-toimintoa.

VALITSE SESSIONTIMEZONEFROM dual;

Tulos:

Australia/Sydney

Tämä toiminto näyttää, että olen Sydneyn aikavyöhykkeellä.

Kuinka voit muuttaa istunnon aikavyöhykettä?

Vaikka et voi helposti muuttaa tietokannan aikavyöhykettä, istunnon aikavyöhyke on helppo muuttaa.

Voit muuttaa istunnon aikavyöhykettä suorittamalla ALTER SESSION -komennon:

ALTER SESSION SET TIME_ZONE = '+8:0';

Mitä eroa on SESSIONTIMEZONE ja CURRENT_TIMESTAMP välillä?

Molemmat toiminnot tarkastelevat istunnon aikaa. Mutta on joitain eroja.

• CURRENT_TIMESTAMP palauttaa koko päivämäärän ja ajan, mukaan lukien aikavyöhykkeen.
• SESSIONTIMEZONE palauttaa vain aikavyöhykkeen.

Joskus SESSIONTIMEZONE voi olla helpompi käyttää kuin yrittää suorittaa merkkijonojen käsittelyä CURRENT_TIMESTAMP-funktiolle.

DBTIMEZONE

DBTIMEZONE-funktio palauttaa tietokannan aikavyöhykesiirron muodossa [+|-]TZH:TZM tai aikavyöhykkeen alueen nimen.

Päivämäärien kanssa työskennellessä on hyödyllistä tietää, missä aikavyöhykkeessä tietokanta on. Tämä on helpompaa kuin käyttää jotakin muuta päivämäärätoimintoa tai poimia TIMESTAMP WITH TIME ZONE -arvosta.

Tämän DBTIMEZONE-toiminnon avulla voit tarkistaa aikavyöhykkeen Oracle-tietokannassa 11g – ja kaikissa muissa tietokantaversioissa.

DBTIMEZONE-funktion syntaksi on melko yksinkertainen:

DBTIMEZONE

On oikeastaan ​​vain yksi tapa käyttää Oraclen DBTIMEZONE-toimintoa.

VALITSE DBTIMEZONEFROM dual;

Tulos:

+00:00

Tämä osoittaa, että tietokantani on UTC + 0 -aikavyöhykkeellä.

Kuinka voit muuttaa tietokannan aikavyöhykettä?

Tietokannan aikavyöhyke on itse asiassa sen palvelimen käyttöjärjestelmän aikavyöhyke, jossa se toimii.

Joten et voi muuttaa tietokannan aikavyöhykettä erikseen.

Voit tarkastella sitä suorittamalla tämän kyselyn:

VALITSE SESSIONTIMEZONE FROM DUAL;

Tai tämä kysely näyttää nykyisen ajan aikavyöhykkeellä:

VALITSE nykyinen_aikaleima FROM DUAL;

Mitä eroa on Oracle DBTIMEZONE:n ja SESSIONTIMEZONE:n välillä Oraclessa?

Ero näiden kahden toiminnon välillä on:

• DBTIMEZONE palauttaa tietokantapalvelimen aikavyöhykkeen.
• SESSIONTIMEZONE palauttaa istunnon aikavyöhykkeen.

Nämä kaksi arvoa voivat olla erilaisia, jos olet eri paikassa kuin tietokanta.

Mitä eroa on Oracle DBTIMEZONE:n ja SYSTIMESTAMP:n välillä?

Ero DBTIMEZONE:n ja SYSTIMESTAMP:n välillä on:

• DBTIMEZONE palauttaa vain tietokannan aikavyöhykkeen poikkeaman (esim. +7:00).
• SYSTIMESTAMP palauttaa aikaleiman tietokannan aikavyöhykkeellä (esim. 13-JUL-2022 08:49:02 AM +7:00)

Joten SYSTIMESTAMP sisältää enemmän tietoa kuin DBTIMEZONE-funktio.

Muotoilupäivämäärät

Toistaiseksi olemme työskennelleet päivämääräarvojen kanssa. Olemme muuttaneet niiden tietotyyppiä, työskennelleet aikavyöhykkeiden kanssa ja jopa poimineet ja lyhentäneet osia niistä.

Olet kuitenkin saattanut huomata, että tietotyypit näytetään tietyssä muodossa. Ja DATE-tietotyyppi ei näytä aikaa, vaikka aika on mukana.

Onneksi Oracle antaa sinun muotoilla tulostellesi DATE-arvon. Tämä voi olla hyödyllistä tarkistaaksesi, palauttaako funktiosi oikean arvon kyselyssä, tai näyttää tulosteen sovelluksessasi.

Voit tehdä tämän TO_CHAR-toiminnolla.

Näytämme esimerkiksi SYSDATE seuraavasti:

SELECT SYSDATEFROM dual;

Tulos:

10.9.22

Jos haluamme nähdä sen eri muodossa, kuten 2022-09-10, voimme kirjoittaa tämän:

SELECT SYSDATE,TO_CHAR(SYSDATE, 'VVVV-KK-PP') AS formatted_dateFROM dual;

Voit nähdä, että tulos on eri muodossa. Siinä on 4 numeroa vuotta, sitten viiva, sitten kaksi numeroa kuukautta, sitten viiva ja sitten kaksi numeroa päivä.

Voit myös käyttää TO_CHAR-toimintoa ajan näyttämiseen. Tämä on yleinen tapa käyttää tätä toimintoa.

SELECT SYSDATE,TO_CHAR(SYSDATE, 'VVVV-KK-PP HH:MI:SS AM') AS formatted_dateFROM dual;

Tämä näyttää saman päivämäärän, mutta eri muodossa. Se näyttää tunnit, minuutit, sekunnit ja AM/PM-merkin.

Nyt luultavasti ihmettelet, mitä kaikki nuo muotoilukoodit tarkoittavat? Jotkut niistä saattavat tuntua ilmeisiltä (kuten YYYY tarkoittaa 4-numeroista vuotta). Mutta entä muut?

Katsotaanpa niitä nyt.

Päivämäärän muodon parametrit

Monissa päivämääriä käsittelevissä Oraclen funktioissa, kuten TO_CHAR, voit määrittää "muotomaskin". Tämä on parametri, jonka avulla voit määrittää tietyn merkkiyhdistelmän, jonka avulla Oracle voi kääntää päivämäärän tiettyyn muotoon.

Voit käyttää näitä merkkejä muuttaaksesi tapaa, jolla päivämäärät muotoillaan.

Täydellinen luettelo päivämäärämuotoparametreista näkyy tässä:

vuosi

Kuukausi

Viikko

Päivä

Aika

Indikaattorit

Päiväystoimintojen yleiset käyttötavat

Olemme siis nähneet selityksiä kaikista Oraclen päivämäärätoiminnoista ja joitain esimerkkejä niiden toiminnasta.

Katsotaanpa nyt joitain yleisiä esimerkkejä päivämääräfunktioista.

Nämä esimerkit osoittavat, kuinka voit tehdä yleisiä tehtäviä Oraclessa päivämäärien avulla. Joten jos etsit, kuinka löytää päivämäärä vuoden kuluttua tai muotoilla päivämäärä tietyllä tavalla, sinun kannattaa etsiä tämä.

Katsotaanpa esimerkkejä.

Etsi päivien lukumäärä kahden päivämäärän välillä

Voit selvittää kahden päivämäärän välisten päivien määrän yksinkertaisesti vähentämällä yhden päivämäärän toisesta. Sinun ei tarvitse käyttää toimintoa tähän.

Jos haluat esimerkiksi selvittää päivien määrän 31. joulukuuta 2022 ja tämän päivän välillä, suorita tämä kysely:

SELECTTO_DATE('31-DEC-2022') - SYSDATEFROM dual;

Tulos:

111.6092245

Lisää päiviä päivämäärään

Aivan kuten vähentäisit kaksi päivämäärää, voit lisätä päivämäärään luvun saadaksesi tulevien päivien määrän. Numeroiden lisäämistä päivämäärään käsitellään päivien lisäämisenä.

SELECTSYSDATE + 7FROM kaksi;

Tulos:

17. syyskuuta 22

Tämä näyttää päivämäärän 7 päivän päästä.

Päivien vähentäminen päivämäärästä

Jos haluat vähentää päivät päivämäärästä, vähennä vain luku. Numero edustaa päivien määrää.

SELECTSYSDATE - 21FROM kaksi;

Tulos:

20.8.22

Tämä näyttää päivämäärän 21 päivää menneisyydessä.

Näytä tämän päivän päivämäärä ja aika

Voit näyttää tämän päivän päivämäärän ja kellonajan käyttämällä SYSDATE-funktiota sekä TO_CHAR-funktiota sen alustamiseen. Vaihtoehtoisesti voit käyttää CURRENT_DATE-funktiota.

SYSDATE näyttää palvelimen päivämäärän ja kellonajan, ja CURRENT_DATE näyttää istunnon päivämäärän ja kellonajan.

Voit kirjoittaa kyselyn näin:

SELECTTO_CHAR(SYSDATE, 'PP-KK-VVVV HH:MI:SS AM')FROM dual;

Tulos:

10-09-2022 09:24:11 AM

Tai voit kirjoittaa saman kyselyn käyttämällä CURRENT_DATE, jos istunnon aika on tärkeämpi (jos esimerkiksi tietokanta on toisella aikavyöhykkeellä ja haluat löytää ajan missä olet).

SELECTTO_CHAR(CURRENT_DATE, 'PP-MM-YYYY HH:MI:SS AM')FROM dual;

Tulos:

10-09-2022 09:24:11 AM

Lisää yksi vuosi päivämäärään

Aiemmin näytin sinulle, kuinka päivämäärä lisätään päivämäärään. Tästä on hyötyä päivien lisäämisessä, mutta vuosien lisäämisessä päivien käyttäminen ei ole luotettavaa.

Tämä johtuu siitä, että kunkin vuoden päivien määrä ei ole johdonmukainen. Monimutkaisen hakutaulukon ja kaavan sijaan voit käyttää vain ADD_MONTHS-funktiota.

Lisää vuosi tähän päivään:

SELECTADD_MONTHS(SYSDATE, 12)FROM dual;

Tulos:

10.9.23

Vähennä vuosia päivämäärästä

Samankaltaisesti kuin vuoden lisääminen päivämäärään, voit myös vähentää vuosia päivämäärästä. Tämä on parasta tehdä käyttämällä ADD_MONTHS-funktiota ja antamalla negatiivinen lukuarvo.

Päivämäärän löytäminen 5 vuotta ennen tiettyä päivämäärää:

SELECTADD_MONTHS('10-JUL-2022', -60)FROM dual;

Tulos:

10.7.17

Huomaa, että lisäsin siihen luvun 60, koska 5 vuotta * 12 kuukautta on 60.

Se voisi olla helpompi lukea ja määrittää, jos käytät vuosilukua * 12:

SELECTADD_MONTHS('10-JUL-2022', 5 * -12)FROM dual;

Tulos:

10.7.17

Tällä tavalla voit vain muuttaa numeroa 5, jos sinun on joskus muutettava tätä, sen sijaan, että lasket, kuinka monta kuukautta vuodessa on.

Etsi kahden päivämäärän välisten kuukausien määrä

Käytä MONTHS_BETWEEN-toimintoa saadaksesi selville kahden päivämäärän välisten kuukausien lukumäärän Oraclessa.

Tästä on apua esimerkiksi selvitettäessä, kuinka monta kuukautta työntekijä on työskennellyt yrityksessä tai kuinka monta kuukautta on kulunut esimerkiksi kaupasta.

SELECTtyöntekijän_tunnus,vuokrauspäivämäärä,ROUND(KUUKAUDEN_VÄLILLÄ(JÄRJESTELMÄN PÄIVÄMÄÄRÄ, VUOKRAUSPÄIVÄMÄÄRÄ), 1) AS kuukausien_työskentelyFROM työntekijä;

Tämä näyttää tunnuksen, palkkauspäivän ja kuukausien lukumäärän, jonka työntekijä on työskennellyt yrityksessä.

Huomaa, että käytin ROUND-funktiota tulosteen puhdistamiseen, koska MONTHS_BETWEEN palauttaa desimaaliluvun, joka voi olla melko pitkä.

Hanki kuukauden ensimmäinen päivä

Voit etsiä kuukauden ensimmäisen päivän Oracle SQL:ssä käyttämällä TRUNC-funktiota.

SELECTTRUNC(SYSDATE, 'MONTH')FROM dual;

Tulos:

1. syyskuuta 22

Hanki kuukauden viimeinen päivä

Voit etsiä kuukauden viimeisen päivän käyttämällä TRUNC- ja LAST_DAY-funktioiden yhdistelmää.

SELECTTRUNC(LAST_DAY(SYSDATE))FROM dual;

Tulos:

30.9.22

Hanki vuoden ensimmäinen päivä

Saadaksesi vuoden ensimmäisen päivän, joka on aina tammikuun 1. päivä, lyhennä se vuodeksi TRUNC-toiminnolla.

SELECTTRUNC(SYSDATE, 'YEAR')FROM dual;

Tulos:

1.1.22

Hanki vuoden viimeinen päivä

Vuoden viimeinen päivä on aina 31. joulukuuta. Voit etsiä vuoden viimeisen päivän SQL:ssä käyttämällä tätä kyselyä:

SELECTADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - 1FROM dual;

Tulos:

31/DEC/22

Tämä kysely katkaisee ensin nykyisen päivämäärän vuoden ensimmäiseksi päiväksi (1. tammikuuta) ja lisää siihen sitten 12 kuukautta (jotta se olisi seuraavan vuoden 1. tammikuuta). Sitten se vähentää siitä 1 päivän löytääkseen kuluvan vuoden viimeisen päivän.

Hanki päivien lukumäärä kuukaudessa

Käytä tätä kyselyä saadaksesi selville tietyn kuukauden päivien lukumäärän, olipa kyseessä kuluva kuukausi tai kuukausi toisesta päivämäärästä.

Se käyttää toimintoja LAST_DAY, TO_CHAR ja CAST.

SELECTCAST(TO_CHAR(LAST_DAY(SYSDATE), 'dd') AS INT)FROM dual;

Tulos:

30

Tämä etsii kuukauden viimeisen päivän käyttämällä LAST_DAY ja muuntaa sen sitten INT-arvoksi. Voit käyttää mitä tahansa päivämäärää tässä SYSDATE:n sijaan.

Johtopäätös

Joten tässä artikkelissa on korostettu kaikkia Oraclen DATE-toimintoja. Olen selittänyt, mitä he tekevät, antanut pienen esimerkin ja käsitellyt muutamia yleisiä ja hyödyllisiä tapoja käyttää näitä päivämäärätoimintoja.

Jos sinulla on kysyttävää näistä toiminnoista, kerro minulle!

Lopuksi, jos pidät tarjoamistani tiedoista ja uraneuvoista, tilaa uutiskirjeeni alla pysyäksesi ajan tasalla artikkeleistani. Saat myös upean bonuksen. Kiitos!