Kako povucite datum iz baze podataka prikladan format d.m.y. (dan.mjesec.godina) Oracle. Pomoći će nam funkcija to_char(). Zahvaljujući njemu, vrijeme možete pretvoriti u željeni format. Pogledajte primjer.
SELECT to_char(current_timestamp, "DD.MM.YYYY") kao date_create iz DUAL
Format vremena DD.MM.GGGG (dan.mjesec.godina). Kao razdjelnike možete koristiti bilo koje znakove: /, :, -.
Za prikaz trenutnog vremena u formatu dan.mjesec.godina sati:minute:sekunde koristite parametre DD.MM.GGGG HH24:MI:SS.
SELECT to_char(current_timestamp, "DD.MM.YYYY HH24:MI:SS") kao date_create iz DUAL /* izlaz: 10/06/2017 16:50:52 */
Recimo da trebate prikazati početak godine? Tekuća godina bit će vam automatski zamijenjena. U ovom primjeru koristio sam ulančavanje ||. Više detaljne informacije pronaći ćete stranicu TO_CHAR FUNCTION. Na toj je stranici veliki popis opcije za pretvaranje datuma u niz.
ODABERITE "01.01." || to_char(current_timestamp, "YYYY") kao date_create iz DUAL
Pregleda: 1762, razina: laka razina, ocjena: 0, datum: 2016-10-26 14:07:32
U ovom odjeljku govorit ćemo o funkcijama za rad s datumom/vremenom i funkcijama za pretvorbu tipa za datume. Za pohranjivanje datuma i vremena, Oracle nudi poseban tip DATE. S fizička točka pogledajte ga razlomački broj, cijeli dio koji pohranjuje broj dana od određenog baznog datuma, a frakcijski pohranjuje vrijeme. To vam omogućuje izvođenje preko datuma aritmetičke operacije- zbrajanje i oduzimanje.
Funkcija SYSDATE
Ovo je jedna od najčešće korištenih funkcija; vraća trenutni datum i vrijeme prema satu poslužitelja. Primjer:
ODABERITE SUSTAVNI DATUM
OD dualnog
SYSDATE
26.12.2007
16:24:43
Funkcija ADD_MONTHS(d, x)
Vraća datum dobiven dodavanjem jednog ili više mjeseci datumu d. Broj mjeseci određen je parametrima x, a x može biti negativan - u tom slučaju navedeni broj mjeseci se oduzima od zadanog datuma.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1, ADD_MONTHS(SYSDATE, -3) d2
OD dualnog
|
26.12.2007 16:24:43 |
26.03.2008 16:24:43 |
26.09.2007 16:24:43 |
LAST_DAY(d) funkcija
Vraća posljednji dan u mjesecu navedenom u datumu d. Primjer:
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
OD dualnog
|
26.12.2007 16:24:43 |
31.12.2008 16:24:43 |
Ova je funkcija vrlo zgodna za određivanje broja dana u određenom mjesecu, na primjer:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), "DD") d1
OD dualnog
|
26.12.2007 16:24:43 |
Funkcija MONTHS_BETWEEN(dl, d2)
Funkcija MONTH_BETWEEN vraća broj mjeseci između dva datuma dl i d2, uzimajući u obzir predznak kao dl-d2, a vraćeni broj je razlomak.
SELECT MONTHS_BETWEEN("2.09.2006", "2.05.2006") d1,
M0NTHS_BETWEEN("12.09.2006", "2.05.2006") d2,
M0NTHS_BETWEEN("2.05.2006", "12.09.2006") d3
OD dualnog
|
4,32258064516129 |
4,32258064516129 |
Funkcija TRUN C (d[,maska ])
Skraćuje navedeni datum prema maski. Ako maska nije navedena, skraćivanje se izvodi prije datuma (vrijeme se odbacuje).
ODABERITE SUSTAVNI DATUMd1,
TRUNC(SYSDATE) d2
OD dualnog
|
26.09.2006 16:45:26 |
Pogledajmo tipične primjere - skraćivanje datuma na sate, dane, mjesec i godinu. Zadana maska formata je "DD"
ODABERITE SUSTAVNI DATUMdl,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "GGGG") d5
OD dualnog
|
26.09.2006 16:49:21 |
26.09.2006 16:00:00 |
26.09.2006 01.09.2006 01.01.2006 |
Funkcija ROUND(d[,maska])
Funkcija ROUND slična je funkciji TRUNC, ali umjesto skraćivanja, zaokružuje. Zadana maska formata je "DD". Primjer:
IZABERISYSDATEd1,
OKRUGLO (SYSDATE)d2,
ROUND(SYSDATE,"
NN24")
d3,
KRUG(SYSDATE, "
dd")
d4,
ROUND(SYSDATE,"MM") d5
OD dualnog
|
26.09.2006 16:50:50 |
27.09.2006 26.09.2006 17:00:00 |
27.09.2006 01.10.2006 |
Maske formata dopuštene za funkcije TRUNC i ROUND
Pogledajmo pobliže maske formata i značajke njihove upotrebe:
|
Svrha |
||
|
Prvi dan stoljeća |
||
|
GODINA, ili GGGG, |
Prvi dan u godini |
|
|
ili YY ili Y |
||
|
Prvi dan kvartala |
||
|
MONTH ili PON, |
Prvi dan u mjesecu |
|
|
Isti dan u tjednu kao i dan tekuće godine |
||
|
Isti dan u tjednu kao i dan u tekućem mjesecu |
||
|
DAN ili DY ili D |
Prvi dan u tjednu |
|
|
NN, ili NN12, ili |
||
Funkcija TO_DATE(str[,mask [,nls_lang]])
Funkcija TO_DATE pretvara niz str u datum. Pretvorba se provodi pomoću maske maske, ako je navedena. Ako maska nije navedena, uzima se zadana maska. Ako navedete masku, možete navesti još jedan parametar - jezik koji se koristi prilikom oblikovanja naziva mjeseci i dana. Ako postoji pogreška pri raščlanjivanju niza str u skladu s navedenim
nastaje maska iznimna situacija. Najčešća pogreška je "ORA-01830: Predložak formata datuma završava prije pretvaranja cijelog niza unosa." Osim toga, često se susreće pogreška "ORA-01821: format datuma nije prepoznat" - pojavljuje se kada je navedena nevažeća maska formata. Primjer:
SELECT T0_DATE("12.09.2006") d
OD dualnog
Funkcija TO_CHAR(d[,maska])
Pretvara datum d u niz znakova prema navedenoj maski. Ako je navedena nevažeća maska, javlja se iznimka "ORA-01821: format datuma nije prepoznat". Primjer:
ODABERITE SUSTAVNI DATUM d1,
TOLCHAR(SYSDATE, "DD.MM.YY HH24:MI") d2
OD dualnog
U početku nisam planirao napisati članak o datumima, već sam se namjeravao usredotočiti na razmatranje samo jednog pitanja na ovu temu. Međutim, tijekom rada ukazala se potreba za osvjetljavanjem raznih dodatne mogućnosti Oracle, počeli su se pojavljivati novi primjeri. Tako je razmatranje jednog pitanja preraslo u mali članak. Nadam se da nije bilo dosadno, unatoč ne najzanimljivijoj temi.
Nalazi se prvi dio članka, posvećen značajkama reda po operatoru, funkcioniranju not in i primjeru implicitne pretvorbe tipa.
to_date funkcija i formati datuma
Malo programera voli teme o formatiranju. Na primjer, neki se tečajevi posebno usredotočuju na oblikovanje datuma i oblikovanje jezika. posljednji sati zadnji dan treninga, jer slušateljima je dosadno. Razlog je u velikim količinama postojeće formate uz njihovu relativno rijetku upotrebu u standardni zadaci. Tri najčešće funkcije koje koriste maske su to_number, to_char i to_date. U sva tri slučaja maska je na drugom mjestu izborni parametar. A ako još uvijek postoji koliko-toliko razuman broj maski za formatiranje brojeva, onda postoji nepristojno velik broj maski za formatiranje datuma, plus sufiksi i modifikatori.Naravno, dostupnost velika količina maske je pozitivna stvar, budući da proširuje mogućnosti, na primjer, možete provjeriti je li 13. rujna 2011. dan programera pomoću maske "DDD" koja vraća broj dana u godini:
--Upit br. 1 odaberite to_char(to_date("09/13/2011"),"DDD") "Dan programera" iz dual;
Unatoč očitim prednostima formatiranja, nisam planirao u drugom dijelu članka uključiti pregled formata datuma i primjere korištenja egzotičnih maski. Prvo, malo je vjerojatno da će to ikoga zanimati, a drugo, autor također nije veliki ljubitelj složenog oblikovanja, jer ga rijetko koristi u životu. Jedini razlog za pojavu ovaj odjeljak– neka pitanja koja čitatelji imaju o korištenju RR formata.
Prije nego prijeđemo izravno na glavnu temu ovog odjeljka, pogledajmo nekoliko nestandardni primjeri rad s datumima.
Primjer br. 1. Korištenje skraćenih predložaka
Počnimo s standardno oblikovanje. Neka današnji datum bude 16.09.2011., hoće li se sljedeći upiti izvršiti i što će vratiti?
--Upit br. 2 odaberite to_char(sysdate, "YYYY") iz dual; --Upit br. 3 odaberite to_date("03", "DD") iz dual;
Upit #2 je tipičan primjer pretvaranja datuma u niz i njegovog pretvaranja u traženi format. Jedina je razlika u tome što smo umjesto poznatijih maski poput "DD.MM.GG" ili "DD-MON-GGGG" koristili masku koja navodi samo godinu. Upit #2 bit će uspješno izvršen i vratit će tekuću godinu u četveroznamenkastom formatu, tj. "2011".
Upit #3 malo je zanimljiviji, to je tipičan primjer eksplicitne konverzije niza u datum s maskom skraćenog formata, tako da je, sa gledišta sintakse, upit točan i uspješno će se izvršiti. Važnije je pitanje rezultat njegove provedbe, tj. koji će se datum vratiti ako se navede samo dan? Prije odgovora ovo pitanje Sjetimo se kako Oracle postavlja vrijeme ako nije eksplicitno postavljeno:
--Upit br. 4 odaberite to_char(to_date("02/03/2011","DD.MM.YYYY"),"DD.MM.YYYY HH24:MI:SS") iz dual; --Upit br. 5 odaberite to_char(to_date("02/03/2011 30", "DD.MM.YYYY MI"),"DD.MM.YYYY HH24:MI:SS") iz dual;
U zahtjevu br. 4 vrijeme nije navedeno, u zahtjevu br. 5 naveden je samo broj minuta, sati i sekunde su izostavljeni. U Oracleu postoji pravilo prema kojem, ako nema vremenske komponente u datumu, vrijeme se automatski postavlja na 00:00:00 (tj. ponoć), ako je naveden samo dio vremenskih elemenata (kao u upitu br. 5), tada su elementi koji nedostaju postavljeni na 00. Stoga će upit br. 4 vratiti niz “02/03/2011 00:00:00”, a upit br. 5 će vratiti “02/03/2011 00:30:00”.
Vratimo se na zahtjev broj 3, je li to istina? ovo pravilo za hurme, tj. Jesu li elementi datuma koji nedostaju tijekom pretvorbe zamijenjeni s 00 ili 01? Da, zamjenjuju se, ali ne svi, točnije za elemente datuma koji nedostaju koriste se vrijednosti iz sysdate-a (prvi dan tekućeg mjeseca tekuće godine). Stoga će upit #3 koristiti 09 kao mjesec i 2011. kao godinu, tako da će rezultat upita biti 09/03/2011.
Primjer br. 2. Redoslijed opcija oblikovanja
Hoće li sljedeći upit uspjeti, i ako hoće, koji će datum vratiti?
--Upit br. 6 odaberite do_date("20092011", "YYYYDDMM") iz dual;
Na prvi pogled, odsutnost graničnika u nizu datuma može se činiti kritičnim čimbenikom koji nije kompatibilan s izvršenjem upita, ali maska datuma također je navedena bez graničnika, a niz koji treba pretvoriti odgovara navedenom uzorku. Stoga će zahtjev br. 6 biti uspješno izvršen i vratit će se 20.11.2009. (format rezultata može se malo razlikovati ovisno o postavkama sesije). Detaljnije ćemo razmotriti probleme vezane uz graničnike u sljedećem primjeru.
Primjer br. 3. Implicitna konverzija
--Upit br. 7 odaberite months_between("1\September-9","01$Oct/08") iz dual;
U ovaj zahtjev navedena su dva parametra niza koji se moraju pretvoriti u datume korištenjem implicitne konverzije. Prema dokumentaciji, kada se koriste zadani formati, niz koji se implicitno pretvara u datum mora zadovoljiti obrazac: separator1 separator2. Većina separatora i separatora mogu se koristiti kao separator1 i separator2 posebni znakovi, uključujući razmake, kartice i " i dvoznake jednostruki citat"". Štoviše, ako redak sadrži najmanje dvije znamenke za određivanje dana, mjeseci i godina, tada se razdjelnik može u potpunosti izostaviti. Na primjer:
--Upit br. 8 odaberite to_date("01September09") iz dual; --Upit br. 9 odaberite to_date("01Sep09") iz dual; --Zahtjev #10 za ovaj zahtjev zadani format datuma trebao bi biti DD.MM.RR odaberite do_datum("010909") iz dual;
Budući da obje linije navedene u zahtjevu br. 7 odgovaraju zadanom uzorku, zahtjev će se uspješno izvršiti i vratiti broj 11.
Primjer br. 4. Parametri funkcije to_date
Neka zadani format datuma bude DD.MON.RR, a jezik datuma ruski, hoće li sljedeći upit funkcionirati:
--Upit br. 11 odaberite do_date(sysdate,"mm/dd/yyyy hh24:mi:ss") iz dual;
Sličan zahtjev pojavio se u jednoj od rasprava na stranici ask Tom. Zamka zahtjeva je u tome što pokušavamo pretvoriti datum (sysdate) u datum. Ako bi zahtjev izgledao ovako:
--Upit br. 12 odaberite to_char(sysdate,"mm/dd/yyyy hh24:mi:ss") iz dual;
To bi izvršenje uspjelo i vratilo bi niz "09/15/2011 23:00:11". Međutim, funkcija to_date očekuje niz kao svoj prvi parametar, tako da prvo implicitno pretvara datum u niz (što je ekvivalentno pozivu to_char(sysdate) sa zadanom maskom). Rezultat ove konverzije je niz "09/15/11", zatim se poziva to_date. Stoga je upit #11 ekvivalentan sljedećem upitu:
--Zahtjev br. 13 odaberite to_date("09/15/11","mm/dd/yyyy hh24:mi:ss") iz dual;
Kao što nije teško provjeriti, zahtjev br. 13 se ne može izvršiti, budući da redak “09.15.11” ne odgovara postavljenoj maski, stoga se zahtjev br. 11 također ne može izvršiti.
Postavite zadani format datuma
Zadani format datuma određen je pomoću dva parametra: NLS_DATE_FORMAT (odgovoran za sam format) i NLS_DATE_LANGUAGE (odgovoran za jezik koji će se koristiti pri ispisivanju naziva dana, mjeseci itd.). Ako ti parametri nisu eksplicitno postavljeni, njihove vrijednosti se postavljaju na temelju NLS_LANG parametra.
Postoje tri razine na kojima možete postaviti format datuma:
- Razina baze podataka: odaberite * iz nls_database_parameters gdje je parametar u ("NLS_DATE_FORMAT", "NLS_DATE_LANGUAGE"); Parametri ove razine postavljaju se prilikom kreiranja baze podataka i zapisuju se u datoteku init.ora.
- Razina instance: odaberite * iz nls_instance_parameters gdje je parametar u ("NLS_DATE_FORMAT", "NLS_DATE_LANGUAGE"); Parametri na ovoj razini postavljaju se kada se instanca pokrene i mogu se promijeniti pomoću naredbe ALTER SYSTEM.
- Razina sesije: odaberite * iz nls_session_parameters gdje je parametar u ("NLS_DATE_FORMAT", "NLS_DATE_LANGUAGE"); Parametri na ovoj razini mogu se promijeniti naredbom ALTER SESSION. Također možete provjeriti vrijednost ovih parametara pomoću upita: odaberite SYS_CONTEXT ("USERENV", "NLS_DATE_FORMAT"), SYS_CONTEXT ("USERENV", "NLS_DATE_LANGUAGE") iz dual;
Primjer br. 5. Format DD.MM.RR u odnosu na DD-MON-RR
Bio sam inspiriran obratiti pozornost na zadano oblikovanje datuma zbog neke neobičnosti u implicitnoj konverziji nizova u datume. razmotrimo sljedeći primjer:
--Zahtjev br. 14 --Postavite zadani format datuma alter session set NLS_DATE_FORMAT="DD.MM.RR"; --Postavite zadani jezik datuma alter set session NLS_DATE_LANGUAGE="AMERICAN"; --Provjerite vrijednost parametara sesije odaberite * iz nls_session_parameters gdje je parametar u ("NLS_DATE_FORMAT", "NLS_DATE_LANGUAGE"); --Pretvori nizove u datum odaberite to_date("11.09.11") iz dual; odaberite do_date("11.SEP.11") iz dual;
Logično je pretpostaviti da će pretvaranje niza “11.09.11” u datum biti uspješno, ali pretvaranje niza “11.SEP.11” neće. Međutim, to nije slučaj; obje pretvorbe će uspjeti. U početku sam pretpostavio da ako je nemoguće pretvoriti niz pomoću maske sesije, Oracle pokušava koristiti maske drugih razina (moja maska razine baze podataka postavljena je na "DD-MON-RR"). Čitajući dokumentaciju pokazalo se da to nije tako, a Oracle se vodi načelima opisanim u prethodnom paragrafu.
Pokušajmo s drugim primjerom:
--Zahtjev br. 15 --Postavite zadani format datuma alter session set NLS_DATE_FORMAT="DD.MON.RR"; --Postavite zadani jezik datuma alter set session NLS_DATE_LANGUAGE="AMERICAN"; --Provjerite vrijednost parametara sesije odaberite * iz nls_session_parameters gdje je parametar u ("NLS_DATE_FORMAT", "NLS_DATE_LANGUAGE"); --Pretvori nizove u datum odaberite to_date("11.09.11") iz dual; odaberite do_date("11.SEP.11") iz dual;
Ako mislite da će rezultat biti identičan prethodnom zahtjevu, varate se. Jedna od konverzija neće uspjeti. U u ovom slučaju niz "11.09.11" ne odgovara uzorku. Možda je ovo misticizam?
Nažalost ne. Čitanje dokumentacije pokazalo je da postoje pravila za automatsku zamjenu elemenata oblikovanja datuma. Ispod je tablica zamjena.
| Element izvornog formata | Dodatni elementi formata koje možete isprobati umjesto izvornika |
|---|---|
| "MM" | "MON" i "MONTH" |
| "PON | "MJESEC" |
| "MJESEC" | "MON" |
| "YY" | "GGGG" |
| "RR" | "RRRR" |
Primjer br. 6. RR naspram YY formata
Većina korisnika dobro poznaje razlike između RR i YY maski, ali ima i onih koji ova informacija bit će korisno. Prijeđimo odmah na primjer. Koje će podatke vratiti sljedeći upiti:
--Zahtjev br. 16 odaberite to_date("11","RR") "RR", to_date("11","YY") "YY" iz dual; --Zahtjev br. 17 odaberite to_date("99","RR") "RR", to_date("99","YY") "YY" iz dual;
Oba gornja upita će se uspješno izvršiti i vratiti datume prema pravilima opisanim u primjeru #1 za zahtjev #3. Dakle, vrijednost dana u svim dohvaćenim datumima bit će 01, a vrijednost mjeseca bit će 09 (ako pokrenete upit u rujnu). Glavno pitanje, kolika će biti vrijednost godine?
Kao što lako možete pretpostaviti, u zahtjevu broj 16 pod “11” mislio sam na 2011. godinu, a vratile su mi obje maske, tj. rezultat upita br. 16 je 01.09.2011. i 01.09.2011.
U zahtjevu broj 17 pod “99” mislio sam na 1999. godinu, a tu su mišljenja maski podijeljena: maska RR je vratila očekivanu godinu 1999., a maska YY vratila je 2099., tj. rezultat upita br. 17 je 01.09.1999. i 01.09.2099.
Pogledajmo detaljnije kako ti elementi oblikovanja rade:
--Zahtjev br. 18 odaberite to_date("00","RR") "00", to_date("49","RR") "49", to_date("50","RR") "50", to_date ( "99","RR") "99" iz dualne unije sve odaberite do_date("00","YY") "00", to_date("49","YY") "49", to_date("50" , "YY") "50", to_date("99","YY") "99" iz dualnog;
Kao što je vidljivo iz zahtjeva br. 18, razlike u radu maski počinju 50-ih godina, tj. YY format uvijek vraća godinu u tekućem stoljeću, a RR uvijek vraća godinu<50 воспринимает как год текущего столетия, а год >50 je kao godina prošlog stoljeća. Zapravo, vrijednosti to_date("99","RR") = 1999 i to_date("00","RR") = 2000 ispravne su samo ako je trenutni datum manji od 2051, nakon čega to_date("99 ","RR" ) = 2099 i to_date("00","RR") = 2100. Ako želite eksperimentirati, možete promijeniti vrijeme poslužitelja na 2051 i vidjeti kako se mijenja rezultat upita br. 18, samo nemojte Ne radite ovo na pokrenutim sustavima! Ako se vrijeme poslužitelja promijeni, promijenit će se i sysdate i bolje je ne razmišljati kakvu ćete glavobolju zadavati svi izvještaji, tablice dnevnika itd. Opća pravila pretvaranje dvoznamenkaste godine u četveroznamenkastu godinu pomoću RR formata izgleda ovako:
Ako je navedena dvoznamenkasta godina od 00 do 49, tada
- Ako su zadnje dvije znamenke tekuće godine od 00 do 49, tada je vraćena godina ima iste prve dvije znamenke kao i tekuća godina.
- Ako su posljednje dvije znamenke tekuće godine od 50 do 99, tada su prve 2 znamenke vraćene godine za 1 veće od prve 2 znamenke tekuće godine.
- Ako su posljednje dvije znamenke tekuće godine od 00 do 49, tada su prve 2 znamenke vraćene godine 1 manje od prve 2 znamenke tekuće godine.
- Ako su posljednje dvije znamenke tekuće godine od 50 do 99, tada vraćena godina ima iste prve dvije znamenke kao i tekuća godina.
--Upit br. 19 odaberite to_date("1950","RR") "50RR", to_date("1950","YY") "50YY" iz dual;
Stoga će upit #19 vratiti 1950 u oba slučaja.
Primjer br. 7. Neki drugi primjeri
Za kraj recenzije, pogledajmo malo egzotike. Hoće li doći do pogreške kao rezultat pokretanja sljedećeg upita:
--Upit br. 20 odaberite DATUM "1928-12-25" iz dual;
Ako ste zaključili da se radi o besmislenom unosu, varate se - radi se o potpuno ispravnom dodjeljivanju datuma u skladu s ANSI standardom, upit br. 20 će se uspješno izvršiti i vratiti 25.12.1928.
Koji od zahtjeva neće biti ispunjen?
--Upit br. 21 odaberite do_date("1998-JAN-25 17:30","YYYY-MON-DD HH24:MI","NLS_DATE_LANGUAGE=AMERICAN") from dual; --Zahtjev br. 21 odaberite to_date("1998-JAN-25 17:30","YYYY-MON-DD HH24:MI","NLS_DATE_LANGUAGE=RUSSIAN") from dual;
Ovaj primjer je namijenjen demonstriranju prisutnosti trećeg parametra u funkciji to_date. Ovaj parametar omogućuje vam postavljanje vrijednosti jednog od NLS (National Language Support) parametara samo za ovaj poziv funkcije to_date. Pogledali smo postavljanje NLS parametara za sesiju u primjeru br. 5. Ključna razlika između zahtjeva br. 20 i br. 21 nije u nazivu mjeseca (maska MON se automatski zamjenjuje maskom MONTH, kao što je opisano u primjeru br. 5), već u indikaciji različiti jezici datumi. Zahtjev br. 21 očekuje naziv mjeseca na engleskom jeziku i, prema tome, neće biti izvršen, zahtjev br. 22 očekuje naziv mjeseca na ruskom jeziku i bit će uspješno izvršen.
Kada bi sljedeća deklaracija tablice uzrokovala pogreške prilikom umetanja podataka?
--Upit br. 23 kreiraj tablicu za_test (broj, b datum zadani za_datum("09/11/2011"));
Svaki put kada sesija sa zadanim formatom datuma "DD.MON.RR" izvršit će umetanje specificirajući samo vrijednost prvog stupca umetanja u for_test(a) values(1); pojavit će se greška.
Značajke prikaza datuma u raznim aplikacijama
Što utječe na prikaz datumaOvaj je odjeljak dodan nakon što je članak objavljen zahvaljujući preporukama danim u komentarima. Sljedeće vrijedi i za prikaz datuma i za prikaz brojeva. Moguće je da ste, kada ste pokrenuli neke od primjera iznad u članku, primili datume u formatu različitom od onog navedenog u rezultatima. Ako se vaše postavke sesije podudaraju s onima navedenima u primjerima, to se čini u najmanju ruku čudnim.
Istina je da prilikom izvršavanja zahtjeva
--Zahtjev br. 24 odaberite sysdate iz dual; dobivate datum, ali da biste prikazali rezultat na ekranu, određeni uslužni program kojim pristupate bazi podataka mora pretvoriti datum u niz. Dakle, za prikaz datuma (i brojeva) implicitno se poziva to_char, tj. imamo klasičan slučaj implicitne konverzije (ova konverzija je samo za prikaz, njeni rezultati ne sudjeluju u nikakvim izračunima i ne utječu ni na što osim na prikaz podataka). Ako postoji implicitna konverzija, onda postoji maska pomoću koje se ona izvodi. U klasičnom slučaju to je mora biti set maski za seansu, tj. maska navedena u parametru NLS_DATE_FORMAT tablice nls_session_parameters, s kojom smo aktivno radili tijekom cijelog članka.
Provjerimo neke aplikacije. Provjerit ćemo pomoću sljedeće skripte:
--Upit br. 25 --Provjera zadanih parametara sesije odaberite * iz nls_session_parameters gdje je parametar u ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); --Izlaz datuma odaberite sysdate iz dual; --Promijenite zadane parametre sesije alter set session NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss"; --Izlaz datuma odaberite sysdate iz dual;
Provjerimo koje parametre sqlplus koristi za prikaz datuma.
Riža. 1. Rezultat izvršavanja upita br. 25 u sqlplusu.
Kao što se može vidjeti na slici 1, format prikaza datuma mijenja se ovisno o postavkama sesije, tj. sqlplus koristi postavke sesije. To olakšava razumijevanje procesa pretvaranja datuma u nizove i obrnuto, jer se iste maske koriste i za konverziju i za prikaz.
Neki napredni razvojni alati koriste vlastite NLS postavke koje nisu povezane s Oracle postavkama. Kao primjer, provjerimo koje parametre PL/SQL Developer koristi za prikaz datuma. Da bismo to učinili, izvršimo upit br. 25 u njemu. 
Riža. 2. Rezultat izvršavanja upita br. 25 u PL/SQL Developeru.
Kao što se može vidjeti na slici 2, format prikaza datuma se ne mijenja kada se promijene postavke sesije. Štoviše, ako pažljivo pogledate, možete vidjeti da i prvi i drugi rezultat prikaza datuma na ekranu nisu odgovarali parametrima sesije (u prvom slučaju prikazani datum je imao godinu u četveroznamenkastom formatu, a maska je označavala godinu u dvoznamenkastom obliku). To znači da uslužni program koristi vlastite postavke NLS-a; u slučaju PL/SQL Developera, njihova lokacija je naznačena na slici. 3. 
Riža. 3. Postavljanje NLS parametara za prikaz datuma u PL/SQL Developeru.
Kako postavke uslužnog programa NLS mogu biti štetne
Prikazivanje datuma u formatu različitom od formata sesije štetno je iz jednog razloga - dovodi korisnika u zabludu i može dovesti do pogrešaka. Pokrenimo sljedeći upit u sqlplus i PL/SQL Developeru:
--Zahtjev br. 26 --Promijenite zadane parametre sesije alter set session NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss"; --Izlaz datuma odaberite sysdate iz dual; --Pokušavamo pretvoriti podatke primljene iz prethodnog zahtjeva u datum select to_date(HHHHHHHH) from dual; U zadnji redak zahtjeva umjesto XXXXXXXXX ubacit ćemo one dobivene od prethodna linija podaci.
Rezultati upita prikazani su na slikama ispod. 
Riža. 4. Rezultat izvršavanja upita br. 26 u sqlplusu.
Riža. 5. Rezultat izvršavanja upita br. 26 u PL/SQL Developeru.
Zašto su podaci prikazani na ekranu u sqlplusu uspješno pretvoreni u datum, ali podaci prikazani na ekranu od strane PL/SQL Developera nisu se mogli pretvoriti? Budući da za pretvorbu Oracle koristi format podataka naveden u sesiji, a izlaz podataka od strane PL/SQL Developera pretvoren je za prikaz u vlastiti format, različit od formata sesije.
Zaključak
Kao zaključak, želio bih vas podsjetiti da u gotovo svakom svom postu posvećenom radu s datumima, Tom Kite govori o potrebi korištenja eksplicitnih konverzija i obaveznom specifikaciji maske. “Kada pretvarate niz u datum, nikada se ne oslanjajte na zadani format datuma, uvijek eksplicitno postavite masku” - to je ono što je rekao. Dodatni primjeri i moguće greške kada radite s konverzijom datuma, možete je pronaći na stranici pitaj Toma.Budući da je rad s datumima zauzeo cijeli članak, mnogi su ostali “preko broda”. zanimljiva pitanja, što bih želio razmotriti. Najvjerojatnije će se treći dio članka pojaviti čim budem imao slobodnog vremena.
U prethodnom smo članku pogledali ugrađene funkcije za rad s nizovima. U ovom članku pričati ćemo o funkcijama za rad s datumom/vremenom i funkcijama za pretvorbu tipa za datume. Za pohranjivanje datuma i vremena, Oracle nudi poseban tip DATE. S fizičke točke gledišta, ovo je razlomački broj, čiji cjelobrojni dio pohranjuje broj dana od nekog baznog datuma, a razlomački dio pohranjuje vrijeme. To vam omogućuje izvođenje aritmetičkih operacija na datumima - zbrajanje i oduzimanje.
Funkcija SYSDATE
Ovo je jedna od najčešće korištenih funkcija, vraća trenutni datum i vrijeme prema satu poslužitelja.
Funkcija LAST_DAY(d)
Vraća posljednji dan u mjesecu navedenom u datumu d.
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
Funkcija MONTHS_BETWEEN(d1, d2)
Funkcija MONTH_BETWEEN vraća broj mjeseci između dva datuma d1 i d2, uzimajući u obzir predznak kao d1-d2, a vraćeni broj je razlomak.
SELECT MONTHS_BETWEEN("2.09.2006", "2.05.2006") d1,
MONTHS_BETWEEN("12.09.2006", "2.05.2006") d2,
MONTHS_BETWEEN("2.05.2006", "12.09.2006") d3
Razmotrimo tipični primjeri- skraćivanje datuma na sate, dane, mjesec i godinu. Zadana maska formata je "DD"
ODABERITE SUSTAVNI DATUM d1,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "GGGG") d5
Maske formata dopuštene za funkcije TRUNC i ROUND
Pogledajmo pobliže maske formata i značajke njihove upotrebe.
|
Maska |
Svrha |
Prvi dan stoljeća |
|
GODINA, ili GGGG, ili GG, ili G |
Prvi dan u godini |
Prvi dan kvartala |
|
MJESEC, ili PON, ili MM |
Prvi dan u mjesecu |
Isti dan u tjednu kao i prvi dan tekuće godine |
|
Isti dan u tjednu kao prvi dan tekućeg mjeseca |
|
DAN ili DY ili D |
Prvi dan u tjednu |
HH, ili HH12, ili HH24 |
|
Funkcija TO_DATE(str[,mask [,nls_lang]])
Funkcija TO_DATE pretvara niz str u datum. Pretvorba se provodi pomoću maske maske, ako je navedena. Ako maska nije navedena, uzima se zadana maska. Ako navedete masku, možete navesti još jedan parametar - jezik koji se koristi prilikom oblikovanja naziva mjeseci i dana. Ako postoji pogreška pri raščlanjivanju niza str prema navedenoj maski, dolazi do iznimke. Najčešća pogreška je "ORA-01830: Predložak formata datuma završava prije pretvaranja cijelog niza unosa." Osim toga, često se susreće pogreška "ORA-01821: format datuma nije prepoznat" - pojavljuje se kada je navedena nevažeća maska formata.
SELECT TO_DATE("12.09.2006") d
Maske formata dopuštene za funkcije TO_CHAR prilikom oblikovanja datuma
Stoljeća, sa znakom minus ispred datuma pr.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE-1000000, "SCC") d2
26.09.2006 17:14:21 |
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "GGGG") d2
Godina, napisana riječima današnjim državnim jezikom.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "YEAR") d2
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "MM") d2
Naziv mjeseca riječima.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "MONTH") d2
Tjedan u godini.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "WW") d2
Dan u godini.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "DDD") d2
Dan u tjednu ispisuje se riječima, po potrebi dopunjenim s do devet znakova s razmacima.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "DAN") d2
Julijanski kalendarski datum. Je li broj dana od 01.01.4712. pr.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "J") d2
Sat u danu na 12-satnoj skali (1-12).
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "HH") d2
Zapisnik (0-59).
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "MI") d2
Ova maska formata korisna je za mjerenje vremenskih intervala u sekundama.
Interpunkcijski znakovi. Oni se izlaze na odgovarajuća mjesta u formatiranom datumu.
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "DD.MM.GGGG HH24.MI") d2,
TO_CHAR(SYSDATE, "DD/MM/GGGG HH12.MI PM") d3
Elementi maske oblikovanja namijenjeni ispisu tekstualne informacije, osjetljivo na velika i mala slova - velika i mala slova kontroliraju velika i mala slova formatiranog teksta. Pogledajmo upravljanje registrom koristeći masku formata DAY kao primjer:
ODABERITE SUSTAVNI DATUM d1,
TO_CHAR(SYSDATE, "DAN") d2,
TO_CHAR(SYSDATE, "Dan") d3,
TO_CHAR(SYSDATE, "dan") d4
26.09.2006 17:47:45 |
Lako je vidjeti da ako je maska napisana velikim slovima, onda je formatirana tekstualna vrijednost vraća velikim slovima. Ako je u donjem, formatirana vrijednost će također biti u donjem. I na kraju, snimanje maske sa veliko slovo uzrokuje da se generirani tekst oblikuje tako da počinje velikim slovom.
Zaključak
U ovom smo članku pogledali sve glavne ugrađene Oracle funkcije dizajnirane za rad s datumima i izvođenje operacija konverzije tipa s tipom "datum". U sljedećem članku pogledat ćemo funkcije pretvorbe brojeva i formatiranja i prijeći na praktične upite pomoću funkcija o kojima smo ranije govorili.
Oracle implementira skup funkcija za rad s vrijednostima datuma/vremena. Nećemo ulaziti u detalje o svim funkcijama, već sažetak u tablici. 1 će vas upoznati sa dostupne mogućnosti. Ako vas bilo koja značajka zanima, kontaktirajte nas. Detaljan opis u imenik Oracle SQL Referenca.
Izbjegavajte korištenje tradicionalnih Oracle funkcije koji obrađuju vrijednosti DATE kada rade s novim tipovima podataka TIMESTAMP. Umjesto njih treba koristiti nove funkcije za tipove INTERVAL ako je moguće. Funkcije DATE trebale bi se koristiti samo za obradu vrijednosti tipa DATE.
Mnogi od onih navedenih u tablici. 1 funkcija (uključujući ADD_MONTHS) prima vrijednosti tipa DATE. Problemi mogu nastati pri korištenju takvih funkcija s novim tipovima podataka TIMESTAMP. Iako se bilo kojoj od ovih funkcija može proslijediti vrijednost tipa TIMESTAMP, Oracle će je implicitno pretvoriti u tip DATE prije nego što funkcija dovrši svoj zadatak, na primjer:
DEKLIRAJ ts TIMESTAMP WITH TIME ZONE; BEGIN ts:= SYSTIMESTAMP; -- Imajte na umu: vrijednost ts varijable navodi -- frakcijske sekunde I vremensku zonu. DBMS_OUTPUT.PUT_LINE(ts); --Promjena ts vrijednosti jedne od ugrađenih funkcija. ts:= POSLJEDNJI_DAN(ts); --Razlomci sekundi su IZGUBLJENI, a vremenska zona zamijenjena je --vremenskom zonom sesije. DBMS_OUTPUT.PUT_LINE(ts); KRAJ;
Proizlaziti:
| Ime | Opis |
| ADD_MONTHS | Vraća vrijednost DATE koja je rezultat povećanja postavljena vrijednost DATUM za navedeni broj mjeseci. Pogledajte Dodavanje i oduzimanje intervala |
| CAST | Pretvara između vrsta podataka - na primjer, između DATE i različita značenja TIMESTAMP. Vidi "CAST and EXTRACT" |
| TRENUTNI DATUM | Vraća trenutni datum i vrijeme u vremenskoj zoni sesije kao vrijednost DATE |
| CURRENT_TIMESTAMP | Vraća trenutni datum i vrijeme u vremenskoj zoni sesije kao vrijednost tipa TIMESTAMP WITH TIME ZONE |
| DBTIMEZONE | Vraća pomak vremenske zone baze podataka u odnosu na UTC u obliku znakovnog niza (na primjer, "-05:00"). Vremenska zona baze podataka koristi se samo kada se radi s vrijednostima tipa TIMESTAMP WITH LOCAL TIME ZONE |
| EKSTRAKT | Vraća vrijednost NUMBER ili VARCHAR2 koja sadrži određeni element datuma/vremena - sat, godinu ili kraticu vremenske zone. Vidi "CAST and EXTRACT" |
| FROM_TZ | Pretvara TIMESTAMP i podatke o vremenskoj zoni u vrijednost tipa TIMESTAMP WITH TIME ZONE |
| ZADNJI DAN | Vraća zadnji dan u mjesecu za zadanu vrijednost DATE |
| LOCALTIMESTAMP | Vraća trenutni datum i vrijeme kao vrijednost TIMESTAMP u lokalnoj vremenskoj zoni |
| MONTHS_BETWEEN | Vraća vrijednost BROJ koja sadrži broj mjeseci između dva datuma. Pogledajte "Izračunavanje intervala između dvije vrijednosti DATE" |
| NEW_TIME | Pretvara vrijednost DATE iz jedne vremenske zone u sličnu vrijednost iz druge vremenske zone. Značajka postoji za održavanje kompatibilnosti sa starijim kodom; nove aplikacije trebaju koristiti TIMESTAMP WITH TIME ZONE ili TIMESTAMP WITH LOCAL TIME ZONE |
| SLJEDEĆI DAN | Vraća datum prvog dana u tjednu koji slijedi nakon navedenog datuma |
| NUMTODSINTERVAL | Pretvara određeni broj dana, sati, minuta ili sekundi (po vašem izboru) u vrijednost tipa INTERVAL IZ DANA U SEKUNDU |
| NUMIGRAČKAMINTERVAL | Pretvara određeni broj godina i mjeseci (po vašem izboru) u vrijednost tipa INTERVAL YEAR TO MONTH |
| KRUG | Vraća vrijednost DATE zaokruženu na navedene jedinice |
| SESSIONTIMEZONE | Vraća pomak vremenske zone sesije (u odnosu na UTC) u obliku znakovnog niza |
| SYS_EXTRACT_UTC | Pretvara vrijednost tipa TIMESTAMP WITH TIME ZONE u vrijednost TIMESTAMP s istim datumom i vremenom, normalizirano prema UTC vremenu |
| SYSDATE | Vraća trenutni datum i vrijeme Oracle poslužitelja kao vrijednost DATE |
| SYSTIMESTAMP | Vraća trenutni datum i vrijeme Oracle poslužitelja kao vrijednost tipa TIMESTAMP WITH TIME ZONE |
| TO_CHAR | Pretvara vrijednost datuma/vremena u znakovni niz. Pogledajte "Pretvorba datuma i vremena" |
| TO_DATE | Pretvara niz znakova u vrijednost DATE. Pogledajte "Pretvorba datuma i vremena" |
| DO_DSINTERVAL | Pretvara znakovni niz u vrijednost tipa INTERVAL DAY TO SECOND. Vidi Intervalne transformacije |
| TO_TIMESTAMP | Pretvara znakovni niz u vrijednost TIMESTAMP. Pogledajte "Pretvorba datuma i vremena" |
| TO_TIMESTAMP_TZ | Pretvara znakovni niz u vrijednost tipa TIMESTAMP WITH TIME ZONE. Pogledajte "Pretvorba datuma i vremena" |
| DO_YMINTERVAL | Pretvara znakovni niz u vrijednost tipa INTERVAL YEAR TO MONTH. Vidi Intervalne transformacije |
| TRUNC | Vraća vrijednost DATE skraćenu na navedene jedinice |
| TZ_OFFSET | Vraća UTC pomak vremenske zone određene nazivom ili kraticom, u obliku VARCHAR2 (na primjer, "-05:00") |
U ovom primjeru varijabla ts sadrži vrijednost tipa TIMESTAMP WITH TIME ZONE. Ova se vrijednost implicitno pretvara u DATE kada prođe LAST_DAY. Budući da tip DATE ne pohranjuje frakcijske sekunde ili pomake vremenske zone, ti se dijelovi ts vrijednosti jednostavno odbacuju. Rezultat LAST_DAY ponovno se dodjeljuje ts, što uzrokuje drugu implicitnu konverziju - ovaj put se DATE pretvara u TIMESTAMP WITH TIME ZONE. Druga konverzija dobiva vremensku zonu sesije, tako da vidimo ?05:00 u rezultirajućem pomaku vremenske zone.
