Cum extrage data din baza de date format convenabil d.m.a. (zi.lună.an) Oracol. Funcția to_char() ne va ajuta. Datorită acesteia, puteți converti ora în formatul dorit. Uită-te la un exemplu.
SELECT to_char(current_timestamp, "DD.MM.YYYY") ca data_create din DUAL
Formatul orei este ZZ.LL.AAAA (zi.lună.an). Puteți folosi orice caracter ca delimitatori: /, :, -.
Pentru a afișa ora curentă în formatul zi.lună.an ore:minute:secunde, utilizați parametrii ZZ.LL.AAAA HH24:MI:SS.
SELECT to_char(current_timestamp, "DD.LL.YYYY HH24:MI:SS") ca date_create din DUAL /* ieșire: 10/6/2017 4:50:52 PM */
Să presupunem că doriți să afișați începutul anului? Veți fi înlocuit automat cu anul curent. În acest exemplu, am folosit || concatenarea. Mai mult informatii detaliate veți găsi pe pagina TO_CHAR FUNCTION. Pe pagina respectivă se află lista mare opțiuni pentru conversia unei date într-un șir.
SELECTAȚI „01.01”. || to_char(current_timestamp, „YYYY”) ca date_create din DUAL
Vizualizări: 1762, Nivel: Ușor, Evaluare: 0 , Data: 26-10-2016 14:07:32
În această secțiune, ne vom concentra pe funcțiile pentru lucrul cu data/ora și pe funcțiile pentru conversia tipurilor pentru date. Oracle oferă un tip DATE special pentru stocarea datelor și orelor. DIN punct fizic vezi-o un număr fracționar, întreaga parte care stochează numărul de zile de la o anumită dată de bază, iar cea fracționată stochează timpul. Acest lucru vă permite să vă angajați peste date operatii aritmetice- adunare si scadere.
Funcția SYSDATE
Aceasta este una dintre cele mai utilizate funcții, returnează data și ora curentă în funcție de ceasul serverului. Exemplu:
SELECTAȚI SYSDATE
DIN dual
SYSDATE
26.12.2007
16:24:43
Funcția ADD_MONTHS(d, x)
Returnează data obținută prin adăugarea uneia sau mai multor luni la data d. Numărul de luni este dat de parametrii x, iar x poate fi negativ - în acest caz, numărul specificat de luni este scăzut de la data dată.
SELECTARE SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1, ADD_MONTHS(SYSDATE, -3) d2
DIN dual
|
26.12.2007 16:24:43 |
26.03.2008 16:24:43 |
26.09.2007 16:24:43 |
Funcția LAST_DAY(d).
Returnează ultima zi a lunii specificată în data d. Exemplu:
SELECTARE SYSDATE d,
LAST_DAY(SYSDATE) d1
DIN dual
|
26.12.2007 16:24:43 |
31.12.2008 16:24:43 |
Această funcție este foarte utilă pentru a determina numărul de zile dintr-o anumită lună, de exemplu:
SELECTARE SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), „DD”) d1
DIN dual
|
26.12.2007 16:24:43 |
Funcția MONTHS_BETWEEN(dl, d2)
Funcția MONTH_BETWEEN returnează numărul de luni dintre două date dl și d2, ajustat cu semn ca dl-d2, numărul returnat este o fracție.
SELECT MONTHS_BETWEEN(„2.09.2006”, „2.05.2006”) d1,
M0NTHS_BETWEEN("09/12/2006", "05/2/2006") d2,
M0NTHS_BETWEEN(„2.05.2006”, „12.09.2006”) d3
DIN dual
|
4,32258064516129 |
4,32258064516129 |
Funcția TRUN C (d[,mask ])
Trunchiază data specificată în funcție de mască. Dacă masca nu este specificată, atunci trunchierea este efectuată la dată (ora este eliminată).
SELECTAȚI SYSDATEd1,
TRUNC(SYSDATE) d2
DIN dual
|
26.09.2006 16:45:26 |
Luați în considerare exemplele tipice - trunchierea unei date în ore, zile, luni și ani. Masca de format implicită este „DD”
SELECTAȚI SYSDATEdl,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, „DD”) d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "AAAA") d5
DIN dual
|
26.09.2006 16:49:21 |
26.09.2006 16:00:00 |
26.09.2006 01.09.2006 01.01.2006 |
Funcția ROUND(d[,mask]).
Funcția ROUND este similară cu TRUNC, dar în loc de trunchiere, efectuează rotunjirea. Masca de format implicită este „DD”. Exemplu:
SELECTAȚISYSDATEd1,
ROUND(SYSDATE)d2,
ROUND(SYSDATE,"
HH24")
d3,
RUNDĂ(SYSDATE, "
DD")
d4,
ROUND(SYSDATE,„MM”) d5
DIN dual
|
26.09.2006 16:50:50 |
27.09.2006 26.09.2006 17:00:00 |
27.09.2006 01.10.2006 |
Formatați măști valabile pentru funcțiile TRUNC și ROUND
Să aruncăm o privire mai atentă la măștile de format și la caracteristicile utilizării lor:
|
Scop |
||
|
Prima zi a secolului |
||
|
AN sau AAAA |
Prima zi a anului |
|
|
sau YY sau Y |
||
|
Prima zi a trimestrului |
||
|
LUNA sau LUNI |
Prima zi a lunii |
|
|
Aceeași zi a săptămânii ca și ziua anului curent |
||
|
Aceeași zi a săptămânii ca și ziua lunii curente |
||
|
DAY sau DY sau D |
Prima zi a săptămânii |
|
|
HH, sau HH12, sau |
||
Funcția TO_DATE(str[,mask [,nls_lang]])
Funcția TO_DATE convertește șirul str într-o dată. Transformarea se realizează conform măștii de mască, dacă este specificată. Dacă masca nu este specificată, atunci este luată masca implicită. Dacă specificați o mască, puteți specifica încă un parametru - limba folosită la formatarea numelor lunilor și zilelor. În cazul unei erori în analizarea șirului de caractere str conform datei
apare masca excepție. Cea mai frecventă eroare este „ORA-01830: modelul de format de dată se termină înainte de a converti întregul șir de intrare”. În plus, eroarea „ORA-01821: formatul de dată nu este recunoscut” nu este neobișnuită - apare atunci când este specificată o mască de format nevalidă. Exemplu:
SELECT T0_DATE("09/12/2006") d
DIN dual
Funcția TO_CHAR(d[,mask])
Convertește data d într-un șir de caractere în funcție de masca dată. Dacă este specificată o mască nevalidă, este aruncată excepția „ORA-01821: formatul de dată nerecunoscut”. Exemplu:
SELECTARE SYSDATE d1,
TOLCHAR(SYSDATE, „ZZ.LL.AA HH24:MI”) d2
DIN dual
Inițial, nu aveam de gând să scriu un articol despre date, dar urma să mă opresc asupra luării în considerare a unei singure probleme pe această temă. Cu toate acestea, în cursul lucrărilor, a devenit necesar să evidențiem diverse caracteristici suplimentare Oracle, noi exemple au început să apară. Deci, luarea în considerare a unei probleme a devenit un articol mic. Sper că nu s-a dovedit plictisitor, în ciuda faptului că nu este cel mai interesant subiect.
Este localizată prima parte a articolului, dedicată particularităților ordinii după operator, operațiunii not in și un exemplu de conversie implicită a tipului.
funcția to_date și formatele de dată
Puțini programatori iubesc subiectul formatării. De exemplu, în unele cursuri, subiectele despre formatarea datei și standardele regionale sunt specifice ultimele ore ultima zi de antrenament, pentru că ascultătorii se plictisesc. Motivul abundenței formatele existente cu utilizarea lor relativ rară în sarcini standard. Cea mai frecventă utilizare a măștilor este în trei funcții: to_number , to_char și to_date . În toate cele trei cazuri, masca este pe locul doi. parametru opțional. Și dacă există un număr mai mult sau mai puțin rezonabil de măști pentru formatarea numerelor, atunci există foarte multe măști pentru formatarea datelor, plus sufixe și modificatori.Cu siguranta disponibilitatea un numar mare măștile este moment pozitiv, deoarece extinde posibilitățile, de exemplu, puteți verifica dacă 13 septembrie 2011 este ziua programatorului, folosind masca „DDD”, care returnează numărul zilei din an:
--Interogarea #1 selectează to_char(to_date("09/13/2011"),"DDD") "Ziua programatorilor" din dual;
În ciuda avantajelor evidente ale formatării, nu am planificat să includ o prezentare generală a formatelor de date și exemple de utilizare a măștilor exotice în a doua parte a articolului. În primul rând, este puțin probabil să fie de interes pentru nimeni și, în al doilea rând, autorul nu este un mare fan al formatării complexe, deoarece o folosește rar în viață. Singurul motiv al apariției aceasta sectiune- câteva întrebări care au apărut de la cititori cu privire la utilizarea formatului RR.
Înainte de a trece direct la subiectul principal al secțiunii, să ne uităm la câteva exemple non-standard lucrul cu datele.
Exemplul #1. Folosind șabloane trunchiate
Sa incepem cu formatare standard. Să fie data de astăzi 16/09/2011, vor fi executate următoarele interogări și ce vor returna?
--Interogarea #2 selectează to_char(sysdate, "YYYY") din dual; --Interogarea #3 selectează to_date ("03", "DD") din dual;
Interogarea nr. 2 este un exemplu tipic de conversie a unei date într-un șir și de difuzare a acesteia formatul dorit. Singura diferență este că în loc de măștile mai familiare precum „ZZ.LL.AA” sau „ZZ-LU-AAAA” am folosit o mască care specifică doar anul. Interogarea #2 va reuși și va returna anul curent în format de patru cifre, de ex. „2011”.
Interogarea #3 este puțin mai interesantă, este un exemplu tipic de conversie explicită a unui șir într-o dată cu o mască de format trunchiată, așa că din punct de vedere al sintaxei, interogarea este corectă și va reuși. O problemă mai importantă este rezultatul implementării sale, adică. la ce data se va intoarce daca se da doar ziua? Înainte de a răspunde această întrebare Să ne amintim cum setează Oracle ora dacă nu este setată în mod explicit:
--Interogarea #4 selectează to_char(to_date("02/03/2011","DD.LL.YYYY"),"DD.LL.YYYY HH24:MI:SS") din dual; --Interogarea #5 selectează to_char(to_date("02/03/2011 30", "DD.LL.YYYY MI"),"DD.LL.YYYY HH24:MI:SS") din dual;
În cererea #4 nu este specificată ora, în cererea #5 este specificat doar numărul de minute, orele și secundele sunt omise. Există o regulă în Oracle, conform căreia, dacă nu există o componentă de timp în dată, atunci ora este setată automat la 00:00:00 (adică miezul nopții), dacă sunt specificate doar o parte din elementele de timp (ca în interogarea nr. 5), apoi elementele lipsă sunt setate la 00. Prin urmare, interogarea #4 va returna șirul „02/03/2011 00:00:00”, iar interogarea #5 va returna „02/03/2011 00 :30:00".
Să revenim la întrebarea #3, este adevărat? această regulă pentru date, adică Elementele date care lipsesc în conversie sunt înlocuite cu 00 sau 01? Da, sunt înlocuite, dar nu toate, mai precis, pentru elementele date lipsă, se folosesc valorile din sysdate (prima zi a lunii curente a anului curent). Prin urmare, interogarea #3 va folosi 09 ca lună și 2011 ca an, deci rezultatul interogării va fi 09/03/2011.
Exemplul #2. Ordinea opțiunilor de formatare
Se va executa următoarea interogare și, dacă da, la ce dată va reveni?
--Interogarea #6 selectează to_date ("20092011", "AAAADDMM") din dual;
La prima vedere, absența separatorilor în șirul de date poate părea a fi un factor critic incompatibil cu execuția interogării, dar masca de dată este specificată și fără separatori, iar șirul de convertit se potrivește cu modelul specificat. Prin urmare, interogarea #6 va reuși și va returna 20.11.2009 (formatul rezultatului poate diferi ușor în funcție de setările sesiunii). Vom analiza problemele legate de delimitatori mai detaliat în exemplul următor.
Exemplul #3. Conversie implicită
--Interogarea #7 selectează months_between("1\Sep-9","01$Oct/08") din dual;
ÎN cererea dată sunt specificați doi parametri șir care trebuie convertiți în date folosind conversia implicită. Conform documentației, atunci când se utilizează formatele implicite, șirul care urmează să fie convertit implicit într-o dată trebuie să se potrivească cu modelul: separator1 separator2. Puteți utiliza majoritatea delimitatorilor ca separator1 și separator2 și caractere speciale, inclusiv spații, file și „ și dublu ghilimele unice„”. Mai mult, dacă șirul conține cel puțin două cifre pentru a specifica zile, luni și ani, atunci separatorul poate fi omis cu totul. De exemplu:
--Interogarea #8 select to_date("septembrie 0109") din dual; --Interogarea #9 select to_date("01Sep09") din dual; --Interogarea #10 pentru această interogare, formatul implicit de dată ar trebui să fie DD.MM.RR select to_date("010909") din dual;
Deoarece ambele șiruri specificate în interogarea #7 se potrivesc cu modelul dat, interogarea va reuși și va returna numărul 11.
Exemplul numărul 4. parametrii funcției to_date
Lăsați formatul implicit de dată să fie DD.MON.RR, iar limba datei să fie rusă, următoarea interogare va funcționa:
--Interogarea #11 selectează to_date(sysdate,"mm/dd/yyyy hh24:mi:ss") din dual;
O solicitare similară a apărut într-una dintre discuțiile de pe pagina întreabă-l pe Tom. Capcana interogării este că încercăm să convertim o dată (sysdate) într-o dată. Dacă cererea arăta astfel:
--Interogarea #12 selectează to_char(sysdate,"mm/dd/yyyy hh24:mi:ss") din dual;
Acea execuție ar avea succes și ar returna șirul „09/15/2011 23:00:11”. Cu toate acestea, funcția to_date așteaptă un șir ca prim parametru, astfel încât data este mai întâi convertită implicit într-un șir (care este echivalent cu apelarea to_char(sysdate) cu o mască implicită). Rezultatul acestei conversii este șirul „09/15/11”, apoi se efectuează apelul to_date. Deci interogarea #11 este echivalentă cu următoarea interogare:
--Interogarea #13 select to_date("09/15/11","mm/dd/yyyy hh24:mi:ss") din dual;
Cum nu este greu de observat, cererea nr. 13 nu poate fi executată, deoarece șirul „09/15/11” nu se potrivește cu masca setată, respectiv cererea nr. 11 nu poate fi executată.
Setarea formatului implicit de dată
Formatul implicit de dată este setat de doi parametri: NLS_DATE_FORMAT (responsabil pentru formatul în sine) și NLS_DATE_LANGUAGE (responsabil pentru limba care va fi folosită la scrierea numelor zilelor, lunilor etc.). Dacă acești parametri nu sunt setați în mod explicit, atunci valorile lor sunt setate pe baza parametrului NLS_LANG.
Există trei niveluri la care puteți seta formatul datei:
- Nivel DB: selectați * din nls_database_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Parametrii acestui nivel sunt setati la crearea bazei de date si sunt inscrisi in fisierul init.ora.
- Nivel de instanță: selectați * din nls_instance_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Parametrii acestui nivel sunt setati la pornirea instantei si pot fi modificati folosind comanda ALTER SYSTEM.
- Nivel de sesiune: selectați * din nls_session_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Parametrii acestui nivel pot fi modificați cu comanda ALTER SESSION. De asemenea, valoarea acestor parametri poate fi verificată folosind interogarea: selectează SYS_CONTEXT ("USERENV", "NLS_DATE_FORMAT"), SYS_CONTEXT ("USERENV", "NLS_DATE_LANGUAGE") din dual;
Exemplul numărul 5. Format DD.MM.RR vs DD-MON-RR
Am fost motivat să acord atenție formatării implicite a datelor de o oarecare ciudățenie în conversia implicită a șirurilor de caractere în date. sa luam in considerare exemplul următor:
--Interogare #14 --Setați formatul implicit de dată pentru modificarea setului de sesiune NLS_DATE_FORMAT="DD.MM.RR"; --Setați limba implicită pentru modificarea sesiunii de modificare a datei NLS_DATE_LANGUAGE="AMERICAN"; --Verificați valoarea parametrilor de sesiune selectați * din nls_session_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); --Convertire șiruri în date select to_date ("9/11/11") din dual; select to_date("11.SEP.11") din dual;
Este logic să presupunem că conversia șirului „09/11/11” într-o dată va reuși, dar șirul „11.SEP.11” nu va reuși. Totuși, acesta nu este cazul, ambele conversii vor reuși. La început, am presupus că, dacă este imposibil să convertești un șir folosind masca de sesiune, Oracle încearcă să folosească măștile altor niveluri (am masca la nivel de bază de date setată la „DD-MON-RR”). Citirea documentației a arătat că nu este cazul, iar Oracle se ghidează după principiile descrise în paragraful anterior.
Să încercăm un alt exemplu:
--Interogare #15 --Setați formatul implicit de dată pentru modificarea setului de sesiune NLS_DATE_FORMAT="DD.MON.RR"; --Setați limba implicită pentru modificarea sesiunii de modificare a datei NLS_DATE_LANGUAGE="AMERICAN"; --Verificați valoarea parametrilor de sesiune selectați * din nls_session_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); --Convertire șiruri în date select to_date ("9/11/11") din dual; select to_date("11.SEP.11") din dual;
Dacă credeți că rezultatul va fi identic cu solicitarea anterioară, atunci vă înșelați. Una dintre transformări va eșua. ÎN acest cazșirul „11.09.11” nu se potrivește cu modelul. Poate e mistic?
Din pacate, nu. Citirea documentației a arătat că există reguli pentru corectarea automată a elementelor de formatare a datei. Mai jos este un tabel cu înlocuiri.
| Element de format original | Elemente de format suplimentare de încercat în locul originalului |
|---|---|
| "MM" | „LUNI” și „LUNA” |
| „LUN | "LUNĂ" |
| "LUNĂ" | "LUNI" |
| "YY" | "AAAA" |
| "RR" | "RRRR" |
Exemplul numărul 6. Format RR vs YY
Majoritatea utilizatorilor sunt bine conștienți de diferențele dintre măștile RR și YY, dar există cei care aceasta informatie se va dovedi util. Să mergem direct la un exemplu. Ce date vor fi returnate de următoarele interogări:
--Interogarea #16 selectează to_date("11","RR") "RR", to_date("11","YY") "YY" din dual; --Interogarea #17 selectează to_date("99","RR") "RR", to_date("99","YY") "YY" din dual;
Ambele interogări de mai sus vor reuși și vor returna date conform regulilor descrise în exemplul #1 pentru interogarea #3. Astfel, valoarea zilei în toate datele primite va fi 01, iar valoarea lunii va fi 09 (dacă rulați interogarea în septembrie). Întrebare principală, care va fi valoarea anului?
Așa cum este ușor de presupus, în cererea nr. 16, prin „11” mă refeream la 2011 și ambele măști mi l-au returnat, i.e. rezultatul interogării #16 este 09/01/2011 și 09/01/2011.
La interogarea nr.17, prin „99” mă refeream la 1999, iar aici părerile măștilor erau împărțite: masca RR a revenit anul așteptat 1999, iar masca YY a revenit 2099, i.e. rezultatul interogării #17 este 09/01/1999 și 09/01/2099.
Să vedem cum funcționează aceste elemente de formatare mai detaliat:
--Interogarea #18 selectează to_date("00","RR") "00", to_date("49","RR") "49", to_date("50","RR") "50", to_date( „99","RR") „99” de la uniunea dublă selectează to_date("00","YY") "00", to_date("49","YY") "49", to_date("50", "YY") "50", to_date ("99","YY") "99" din dual;
După cum puteți vedea din cererea #18, diferențele în funcționarea măștilor încep din anii 50, adică. Formatul YY returnează întotdeauna anul din secolul curent și RR anul<50 воспринимает как год текущего столетия, а год >50 este ca anul secolului trecut. De fapt, valorile to_date("99","RR") = 1999 și to_date("00","RR") = 2000 sunt corecte numai dacă data curentă este mai mică decât 2051, după aceasta to_date ("99", "RR" ) = 2099 și to_date("00","RR") = 2100. Dacă doriți să experimentați, puteți modifica ora serverului la 2051 și puteți vedea cum se modifică rezultatul interogării #18, pur și simplu nu faceți pe sisteme live! Dacă se schimbă ora serverului, sysdate se va schimba și este mai bine să nu te gândești la ce fel de durere de cap vei avea în toate rapoartele, tabelele de jurnal etc. Reguli generale convertirea unui an cu două cifre într-un an cu patru cifre folosind formatul RR arată astfel:
Dacă anul specificat cu două cifre este de la 00 la 49, atunci
- Dacă ultimele două cifre ale anului curent sunt de la 00 la 49, atunci anul returnat are aceleași primele două cifre ca anul curent.
- Dacă ultimele două cifre ale anului curent sunt de la 50 la 99, atunci primele 2 cifre ale anului returnat sunt cu 1 mai mari decât primele 2 cifre ale anului curent.
- Dacă ultimele două cifre ale anului curent sunt de la 00 la 49, atunci primele 2 cifre ale anului returnat sunt cu 1 mai mici decât primele 2 cifre ale anului curent.
- Dacă ultimele două cifre ale anului curent sunt de la 50 la 99, atunci anul returnat are aceleași primele două cifre ca anul curent.
--Interogarea #19 selectează to_date("1950","RR") "50RR", to_date("1950","YY") "50YY" din dual;
Deci interogarea #19 va returna 1950 în ambele cazuri.
Exemplul numărul 7. Alte exemple
La sfârșitul recenziei, să considerăm puțin exotic. Va apărea o eroare ca urmare a executării următoarei interogări:
--Interogarea #20 selectați DATA „1928-12-25” din dual;
Dacă decideți că aceasta este o înregistrare fără sens, atunci vă înșelați - aceasta este o atribuire complet corectă a datei în conformitate cu standardul ANSI, interogarea # 20 va reuși și va returna 25/12/1928.
Care dintre cereri va eșua?
--Interogarea #21 select to_date("1998-JAN-25 17:30","AAAA-LUN-DD HH24:MI","NLS_DATE_LANGUAGE=AMERICAN") din dual; --Interogarea #21 select to_date("1998-JAN-25 17:30","AAAA-LUN-DD HH24:MI","NLS_DATE_LANGUAGE=RUSSIAN") din dual;
Acest exemplu are scopul de a demonstra prezența unui al treilea parametru în funcția to_date. Acest parametru vă permite să setați valoarea unuia dintre parametrii NLS (National Language Support) numai pentru acest apel la funcția to_date. Setarea parametrilor NLS pentru sesiune, am luat în considerare în exemplul nr. 5. Diferența cheie dintre interogările #20 și #21 nu este în numele lunii (masca LUN este înlocuită automat cu masca LUNA, așa cum este descris în exemplul #5), ci în indicație limbi diferite datele. Solicitarea #21 așteaptă numele lunii în limba engleză și, în consecință, nu va fi executată, cererea #22 așteaptă numele lunii în limba rusă și va fi executată cu succes.
Când următoarea declarație de tabel ar cauza erori la inserarea datelor?
--Interogarea #23 creați tabel pentru_test (un număr, b data implicită la_date("09/11/2011"));
De fiecare dată când o sesiune cu formatul implicit de dată „DD.MON.RR” se va insera doar cu valoarea primei coloane inserată în valorile for_test(a)(1); va apărea o eroare.
Caracteristici de afișare a datelor în diverse aplicații
Ce afectează afișarea dateiAceastă secțiune a fost adăugată după publicarea articolului datorită recomandărilor indicate în comentarii. Următoarele sunt valabile atât pentru afișarea datelor, cât și pentru afișarea numerelor. Este posibil ca atunci când ați rulat unele dintre exemplele de mai sus în articol, să aveți date într-un alt format decât cel afișat în rezultate. Dacă setările sesiunii tale corespundeau cu cele specificate în exemple, atunci acest lucru pare cel puțin ciudat.
Adevărul este că atunci când executați o interogare
--Solicitarea #24 selectați data de sistem din dual; primești o dată, dar pentru a afișa rezultatul pe ecran, utilitarul specific cu care accesezi baza de date trebuie să convertească data într-un șir. Astfel, pentru a afișa datele (și numerele), to_char este implicit numit, i.e. avem un caz clasic de conversie implicită (aceasta este o conversie doar pentru afișare pe ecran, rezultatele acesteia nu participă la niciun calcul și nu afectează altceva decât afișarea datelor). Dacă există o conversie implicită, atunci există și o mască prin care se realizează. În cazul clasic, aceasta trebuie să fie masca setata pentru sedinta, i.e. masca specificată în parametrul NLS_DATE_FORMAT din tabelul nls_session_parameters, cu care am lucrat pe larg pe parcursul articolului.
Să testăm câteva aplicații. Vom verifica folosind următorul script:
--Interogare #25 --Verificați parametrii impliciti de sesiune selectați * din nls_session_parameters unde parametrul este ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); --Output data select sysdate from dual; --Schimbați parametrii impliciti de sesiune alterează setul de sesiune NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss"; --Output data select sysdate from dual;
Să verificăm ce parametri folosește sqlplus pentru a afișa datele.
Orez. 1. Rezultatul interogării #25 în sqlplus.
După cum puteți vedea din Fig.1, formatul de afișare a datei se modifică în funcție de setările sesiunii, de ex. sqlplus utilizează setările de sesiune. Acest lucru face mai ușor de înțeles procesul de conversie a datelor în șiruri și înapoi, deoarece aceleași măști sunt folosite atât pentru conversie, cât și pentru afișare.
Unele instrumente avansate de dezvoltare folosesc propriile setări NLS care nu sunt legate de setările Oracle. De exemplu, să verificăm ce opțiuni folosește PL/SQL Developer pentru a afișa datele. Pentru a face acest lucru, vom executa interogarea nr. 25 în ea. 
Orez. 2. Rezultatul interogării #25 în PL/SQL Developer.
După cum se poate observa din Figura 2, formatul de afișare a datei nu se schimbă atunci când se modifică setările sesiunii. Mai mult, dacă te uiți cu atenție, poți observa că atât primul, cât și cel de-al doilea rezultat al afișarii datei pe ecran nu se potriveau cu parametrii sesiunii (în primul caz, data afișată avea un an în format de patru cifre, iar masca indica anul în format de două cifre). Aceasta înseamnă că utilitarul folosește propriile setări NLS, în cazul dezvoltatorului PL/SQL, locația acestora este prezentată în Fig. 3. 
Orez. 3. Setarea parametrilor NLS pentru afișarea datelor în PL/SQL Developer.
Cum pot fi dăunătoare setările utilitarului NLS
Afișarea datei într-un alt format decât formatul de sesiune este dăunătoare dintr-un singur motiv - induce în eroare utilizatorul și poate duce la erori. Rulați următoarea interogare în sqlplus și PL/SQL Developer:
--Solicitare #26 --Modificarea parametrilor impliciti de sesiune alterează setul de sesiune NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss"; --Output data select sysdate from dual; --Încercarea de a converti datele primite de la interogarea anterioară într-o dată select to_date(ХХХХХХХХ) din dual; În ultima linie a interogării, în loc de XXXXXXXXXX, vom introduce mesajul primit de la linia anterioară date.
Rezultatele executării interogării sunt prezentate în figurile de mai jos. 
Orez. 4. Rezultatul interogării #26 în sqlplus.
Orez. 5. Rezultatul executării interogării #26 în PL/SQL Developer.
De ce în sqlplus datele afișate pe ecran au fost convertite cu succes într-o dată, dar datele afișate pe ecran de către PL/SQL Developer nu au putut fi convertite? Deoarece Oracle folosește formatul de date specificat în sesiune pentru conversie, iar datele de ieșire de către PL/SQL Developer au fost aduse să fie afișate în format propriu, diferit de formatul de sesiune.
Concluzie
În concluzie, vreau să vă reamintesc că în aproape fiecare postare despre lucrul cu datele, Tom Kite vorbește despre necesitatea folosirii conversiilor explicite și a indicației obligatorii a măștii. „Când convertiți un șir într-o dată, nu vă bazați niciodată pe formatul implicit de dată, setați întotdeauna în mod explicit masca” - ceva de genul acesta sună ca cuvintele lui. Exemple suplimentare și posibile greșeli atunci când lucrați cu conversii de date, îl puteți găsi utilizând pagina Întrebați-l pe Tom.Deoarece lucrul cu date a ocupat întregul articol, o mulțime de intrebari interesante pe care aș dori să le iau în considerare. Cel mai probabil, a treia parte a articolului va apărea imediat ce voi avea timp liber.
În articolul precedent, ne-am uitat la funcțiile încorporate pentru lucrul cu șiruri. În acest articol vom vorbi despre functii de lucru cu data/ora si functii de conversie a tipurilor pentru data. Oracle oferă un tip DATE special pentru stocarea datelor și orelor. Din punct de vedere fizic, acesta este un număr fracționar, a cărui parte întreagă stochează numărul de zile de la o anumită dată de bază, iar partea fracțională stochează timpul. Acest lucru vă permite să efectuați operații aritmetice pe date - adunare și scădere.
Funcția SYSDATE
Aceasta este una dintre cele mai frecvent utilizate funcții, se întoarce data curentași timpul în funcție de ceasul serverului.
Funcția LAST_DAY(d).
Returnează ultima zi a lunii specificată în data d.
SELECTARE SYSDATE d,
LAST_DAY(SYSDATE) d1
Funcția MONTHS_BETWEEN(d1, d2)
Funcția MONTH_BETWEEN returnează numărul de luni dintre două date d1 și d2, semnate ca d1-d2, numărul returnat este o fracție.
SELECT MONTHS_BETWEEN(„2.09.2006”, „2.05.2006”) d1,
MONTHS_BETWEEN("09/12/2006", "05/2/2006") d2,
MONTHS_BETWEEN(„2.05.2006”, „12.09.2006”) d3
Considera exemple tipice- Trunchiați data la ore, zile, lună și an. Masca de format implicită este „DD”
SELECTARE SYSDATE d1,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, „DD”) d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "AAAA") d5
Formatați măști valabile pentru funcțiile TRUNC și ROUND
Să aruncăm o privire mai atentă asupra măștilor de format și a caracteristicilor aplicației lor.
|
Masca |
Scop |
Prima zi a secolului |
|
AN sau YYYY sau YY sau Y |
Prima zi a anului |
Prima zi a trimestrului |
|
LUNA sau LUN sau MM |
Prima zi a lunii |
Aceeași zi a săptămânii ca prima zi a anului curent |
|
Aceeași zi a săptămânii ca prima zi a lunii curente |
|
DAY sau DY sau D |
Prima zi a săptămânii |
HH sau HH12 sau HH24 |
|
Funcția TO_DATE(str[,mask [,nls_lang]])
Funcția TO_DATE convertește șirul str într-o dată. Transformarea se realizează conform măștii de mască, dacă este specificată. Dacă masca nu este specificată, atunci este luată masca implicită. Dacă specificați o mască, puteți specifica încă un parametru - limba folosită la formatarea numelor lunilor și zilelor. Dacă există o eroare în analizarea șirului de caractere str în funcție de masca specificată, se aruncă o excepție. Cea mai frecventă eroare este „ORA-01830: modelul de format de dată se termină înainte de a converti întregul șir de intrare”. În plus, eroarea „ORA-01821: formatul de dată nu este recunoscut” nu este neobișnuită - apare atunci când este specificată o mască de format nevalidă.
SELECTARE TO_DATE("09/12/2006") d
Formatați măști valabile pentru funcțiile TO_CHAR în cazul formatării datei
Un secol, cu semnul minus înainte de datele î.Hr.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE-1000000, „SCC”) d2
26.09.2006 17:14:21 |
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „AAAA”) d2
Anul scris în cuvinte, ținând cont de limba națională actuală.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „YEAR”) d2
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MM”) d2
Numele lunii în cuvinte.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MONTH”) d2
saptamana din an.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „WW”) d2
Ziua anului.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DDD”) d2
Ziua săptămânii în cuvinte, completată la nouă caractere cu spații dacă este necesar.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DAY”) d2
data calendarului iulian. Este numărul de zile de la 01/01/4712 î.Hr.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „J”) d2
Ora din zi pe o scară de 12 ore (1-12).
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „HH”) d2
Minute (0-59).
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „MI”) d2
Această mască de format este utilă pentru măsurarea intervalelor de timp în secunde.
Semne de punctuatie. Acestea sunt scoase în locurile corespunzătoare din data formatată.
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „ZZ.LL.AAAA HH24.MI”) d2,
TO_CHAR(SYSDATE, „ZZ/LL/AAAA HH12.MI PM”) d3
Formatați elementele de mască destinate ieșirii informații text, diferențiat cu majuscule și minuscule - majuscule și minuscule controlează majusculele textului formatat. Luați în considerare gestionarea registrelor folosind masca format DAY ca exemplu:
SELECTARE SYSDATE d1,
TO_CHAR(SYSDATE, „DAY”) d2,
TO_CHAR(SYSDATE, „Ziua”) d3,
TO_CHAR(SYSDATE, „zi”) d4
26.09.2006 17:47:45 |
Este ușor de observat că dacă masca este scrisă cu majuscule, atunci cea formatată valoarea textului revine cu majuscule. Dacă în partea de jos - valoarea formatată va fi și în partea de jos. Și în sfârșit, scriind masca cu majusculă face ca textul generat să fie formatat astfel încât să înceapă cu o literă mare.
Concluzie
În acest articol, am acoperit toate funcțiile Oracle principale încorporate pentru lucrul cu date și efectuarea operațiunilor de conversie a tipurilor cu tipul „date”. În articolul următor, ne vom uita la funcțiile de conversie și formatare a numerelor și vom trece la interogări practice care folosesc funcțiile discutate mai devreme.
Oracle implementează un set de funcții pentru lucrul cu valori de dată/ora. Nu vom intra în detaliu despre toate funcțiile, ci rezumatul din Tabel. 1 vă voi prezenta oportunități disponibile. Dacă sunteți interesat de oricare dintre funcții, vă rugăm să contactați descriere detaliata la director Oracle SQL referinţă.
Evitați utilizarea tradițională Funcții Oracle, gestionând valorile DATE atunci când lucrați cu noile tipuri de date TIMESTAMP. În schimb, utilizați noile funcții pentru tipurile INTERVAL ori de câte ori este posibil. Și funcțiile DATE ar trebui folosite numai pentru a procesa valori de tip DATE.
Multe dintre cele prezentate în tabel. 1 funcții (inclusiv ADD_MONTHS) primesc valori DATE. Pot apărea probleme la utilizarea unor astfel de funcții cu noile tipuri de date TIMESTAMP. Deși oricare dintre aceste funcții i se poate transmite o valoare de tip TIMESTAMP , Oracle o va converti implicit într-un tip DATE și numai atunci funcția își va finaliza sarcina, de exemplu:
DECLARE TS TIMESTAMP CU FUS ORAR; BEGIN ts:= SYSTIMESTAMP; --Rețineți că valoarea variabilei ts este setată la --fractional seconds ȘI fusul orar. DBMS_OUTPUT.PUT_LINE(ts); --Schimbați valoarea lui ts într-una dintre funcțiile încorporate. ts:= LAST_DAY(ts); -- Fracțiunile de secunde sunt PIERDUTE, iar fusul orar este înlocuit cu -- fusul orar al sesiunii. DBMS_OUTPUT.PUT_LINE(ts); SFÂRȘIT;
Rezultat:
| Nume | Descriere |
| ADD_MONTHS | Returnează valoarea DATE rezultată din increment valoarea stabilită DATE pentru numărul dat de luni. Consultați Adunarea și scăderea intervalelor |
| CAST | Efectuează conversii între tipuri de date - de exemplu, între DATE și sensuri diferite TIMESTAMP-UL . Vedeți „CAST și EXTRACT” |
| DATA CURENTA | Returnează data și ora curente din fusul orar al sesiunii ca valoare DATE |
| CURRENT_TIMESTAMP | Returnează data și ora curentă în fusul orar al sesiunii ca valoare de tip TIMESTAMP WITH TIME ZONE |
| DBTIMEZONE | Returnează decalajul fusului orar al bazei de date față de UTC ca șir de caractere (de exemplu, „-05:00”). Fusul orar al bazei de date este utilizat numai atunci când se lucrează cu valori de tip TIMESTAMP WITH LOCAL TIME ZONE |
| EXTRAGE | Returnează o valoare NUMBER sau VARCHAR2 care conține elementul dată/oră specificat - abreviere oră, an sau fus orar. Vedeți „CAST și EXTRACT” |
| FROM_TZ | Convertește TIMESTAMP și datele fusului orar la o valoare de tip TIMESTAMP WITH TIME ZONE |
| ULTIMA ZI | Returnează ultima zi a lunii pentru valoarea de intrare DATE dată |
| LOCALTIMESTAMP | Returnează data și ora curente ca valoare TIMESTAMP în fusul orar local |
| LUNII_ ÎNTRE | Returnează o valoare NUMBER care conține numărul de luni dintre două date. Consultați „Calculul intervalului dintre două valori DATE” |
| TIMP NOU | Convertește o valoare DATE dintr-un fus orar într-o valoare DATE dintr-un alt fus orar. Funcția există pentru a menține compatibilitatea cu codul mai vechi; aplicațiile noi ar trebui să utilizeze tipurile de TIMESTAMP WITH TIME ZONE sau TIMESTAMP WITH TIME ZONE |
| ZIUA URMATOARE | Returnează data primei zile a săptămânii următoare datei specificate |
| NUMTODSINTERVAL | Convertește un anumit număr de zile, ore, minute sau secunde (alegerea dvs.) într-o valoare de tip INTERVAL DAY TO SECOND |
| NUMTOYMINTERVAL | Convertește un anumit număr de ani și luni (la alegere) într-o valoare de tip INTERVAL YEAR TO MONTH |
| RUNDĂ | Returnează o valoare DATE rotunjită la unitățile date |
| SESSIONTIMEZONE | Returnează decalajul fusului orar al sesiunii (față de UTC) ca șir de caractere |
| SYS_EXTRACT_UTC | Convertește o valoare de tip TIMESTAMP WITH TIME ZONE într-o valoare TIMESTAMP cu aceeași dată și oră, normalizată la ora UTC |
| SYSDATE | Returnează data și ora curente ale serverului Oracle ca valoare DATE |
| SYSTIMESTAMP | Returnează data și ora curente ale serverului Oracle ca valoare de tip TIMESTAMP WITH TIME ZONE |
| TO_CHAR | Convertește o valoare dată/ora într-un șir de caractere. Consultați „Conversia datei și orei” |
| PÂNĂ ÎN PREZENT | Convertește un șir de caractere într-o valoare DATE. Consultați „Conversia datei și orei” |
| TO_DSINTERVAL | Convertește un șir de caractere într-o valoare de tip INTERVAL DAY TO SECOND . Consultați Conversii de spațiere |
| TO_TIMESTAMP | Convertește un șir de caractere într-o valoare TIMESTAMP. Consultați „Conversia datei și orei” |
| TO_TIMESTAMP_TZ | Convertește un șir de caractere într-o valoare de tip TIMESTAMP WITH TIME ZONE . Consultați „Conversia datei și orei” |
| TO_YMINTERVAL | Convertește un șir de caractere într-o valoare de tip INTERVAL YEAR TO MONTH . Consultați Conversii de spațiere |
| TRUNC | Returnează o valoare DATE trunchiată la unitățile date |
| TZ_OFFSET | Returnează decalajul UTC al fusului orar dat prin nume sau abreviere, în formă VARCHAR2 (de exemplu, „-05:00”) |
În acest exemplu, variabila ts conține o valoare de tip TIMESTAMP WITH TIME ZONE . Această valoare este convertită implicit în DATE când trece LAST_DAY . Deoarece tipul DATE nu stochează părțile fracționale de secundă sau decalajul fusului orar, aceste părți ale valorii ts sunt pur și simplu eliminate. Rezultatul LAST_DAY este din nou atribuit lui ts , ceea ce are ca rezultat o a doua conversie implicită - de data aceasta DATE este convertită în TIMESTAMP WITH TIME ZONE . A doua transformare primește fusul orar al sesiunii, așa că vedem ?05:00 în decalajul fusului orar în valoarea finală.
