Functii asupra datelor calendaristice

Una dintre caracteristicile importante ale Oracle este abilitatea de a memora si opera cu date calendaristice. Tipurile de date calendaristice recunoscute de Oracle sunt:

DATE - valorile avand acest tip sunt memorate intr-un format intern specific, care include pe langa ziua, luna si anul, de asemenea ora, minutul, si secunda.

TIMESTAMP - valorile avand acest tip memoreaza data calendaristica, ora, minutul si secunda dar si fractiunea de secunda.

TIMESTAMP WITH [LOCAL] TIME ZONE - este similar cu TIMESTAMP, insa se va memora si diferenta de fus orar fata de ora universala, a orei de pe serverul bazei de date, sau a aplicatiei client, in cazul in care se include optiunea LOCAL

INTERVAL YEAR TO MONTH - memoreaza o perioada de timp in ani si luni.

INTERVAL DAY TO SECOND - memoreaza un interval de timp in zile, ore, minute si secunde.

Sa exemplificam aceste tipuri de date creand o tabela de test cu comanda:

create table test3

(data1 DATE, data2 TIMESTAMP(5),

data3 TIMESTAMP(5) WITH TIME ZONE,

data4 TIMESTAMP(5) WITH LOCAL TIME ZONE)

Vom insera acum o linie noua in aceasta tabela:

insert into test3

values(sysdate,systimestamp,systimestamp,systimestamp)

si la afisarea tabelei

select * from test3

vom obtine rezultatul din figura II.2.3.

Figura II.2.3

Aritmetica datelor calendaristice

Oracle stie sa realizeze operatii aritmetice asupra datelor calendaristice, astfel adaugarea valorii la o data calendaristica, va duce la obtinerea urmatoarei date calendaristice:

SELECT sysdate, sysdate+5, sysdate-70 from dual

Figura II.2.4. Adunarea unui numar intreg la o data calendaristica

De asemenea se poate face diferenta dintre doua date calendaristice, obtinandu-se numarul de zile dintre cele doua date:

SELECT first_name, last_name,

hire_date, sysdate-hire_date

FROM employees

Figura II.2.5. Diferenta dintre doua date calendaristice

Desi implicit o data calendaristica de tip DATE nu este afisata in format complet (nu se afiseaza ora, minutul, secunda), in tabela se memoreaza complet. De aceea poate fi uneori derutant rezultatul unor operatii aritmetice cu date calendaristice, dupa cum se vede in figura II.2.6. in care diferenta dintre ziua de astazi si cea de ieri este de

SELECT sysdate-TO_DATE('20-APR-07','dd-MON-yy') FROM dual

Figura II.2.6.

De ce se obtine acest lucru? Simplu, data de 20 aprilie a fost precizata fara ora, asadar a fost considerata implicit ora . Iar sysdate ne-a furnizat data curenta incluzand si ora. Asadar de ieri de la ora pana astazi la ora a trecut mai mult de o zi.

Functii cu date calendaristice

Oracle ofera un numar foarte mare de functii care opereaza asupra datelor calendaristice, dar in cele ce urmeaza ne vom opri asupra celor mai importante dintre acestea.

SYSDATE - returneaza data si ora curenta a serverului bazei de date.

CURRENT_DATE - returneaza data si ora curenta a aplicatiei client. Aceasta poate sa difere de data bazei de date.

SYSTIMESTAMP - returneaza data in formatul TIMESTAMP

select CURRENT_DATE, sysdate, systimestamp

from dual

Figura II.2.7. Functiile SYSDATE CURRENT_DATE si SYSTIMESTAMP

ADD_MONTHS(data,nrluni) - adauga un numar de luni la data curenta. Daca al doilea parametru este un numar negativ, se realizeaza de fapt scaderea unui numar de luni din data precizata.

MONTHS_BETWEEN(data1,data2) - determina numarul de luni dintre doua date calendaristice precizate. Rezultatul returnat poate fi un numar real (vezi figura II.2.8). Daca prima data este mai mica (o data mai veche) atunci rezultatul va un numar negativ.

select sysdate, hire_date,

MONTHS_BETWEEN(sysdate, hire_date),

MONTHS_BETWEEN(hire_date, sysdate)

from employees

Figura II.2.8. Functia MONTHS_BETWEEN

LEAST(data1,data2,.) - determina cea mai veche (cea mai mica) data dintre cele transmise ca parametru.

GREATEST(data1,data2,.) - determina cea mai recenta (cea mai mare) data dintre cele transmise ca parametru.

select hire_date,sysdate,

least(hire_date,sysdate),greatest(hire_date,sysdate)

from employees

Figura II.2.9. Functiile LEAST si GEATEST

NEXT_DAY(data, 'ziua') - returneaza urmatoarea data de 'ziua' de dupa data transmisa ca parametru, unde 'ziua' poate fi 'Monday' 'Tuesday' etc. In exemplele care urmeaza data curenta este considerata ziua de marti, 27 februarie 2007.

LAST_DAY(data) - returneaza ultima zi din luna din care face parte data transmisa ca parametru.

ROUND(data,'format') - daca nu se precizeaza formatul, functia rotunjeste data transmisa ca parametru la cea mai apropiata ora 12 AM, adica daca ora memorata in data este inainte de miezul zilei atunci se va returna ora 12 AM a datei transmise. Daca ora memorata in data este dupa miezul zilei se va returna ora 12 AM a zilei urmatoare.

select to_char(sysdate,'dd-MON-YY hh:mi AM'),

round(sysdate) from dual

Figura II.2.10. Functia ROUND

In cazul in care este specificat formatul, data va fi rotunjita conform formatului indicat. Cateva dintre formatele cele mai uzuale sunt:

y yy yyyy year - se rotunjeste data la cea mai apropiata data de 1 Ianuarie. Daca data este inainte de 1 iulie, se va returna data de 1 ianuarie a aceluiasi an. Daca data este dupa data de 1 iulie se va returna data de 1 ianuarie a anului urmator.

mm month - rotunjeste data la cel mai apropiat inceput de luna. Orice data calendaristica aflata dupa data de , inclusiv, este rotunjita la prima zi a lunii urmatoare.

ww week - se rotunjeste data la cel mai apropiat inceput de saptamana. Prima zi a saptamanii este considerata lunea. Pentru datele aflate dupa ziua de joi, inclusiv, se va returna ziua de luni a saptamanii urmatoare.

TRUNC(data,'format') - truncheaza data specificata conform formatului specificat. Se pot folosi aceleasi formate ca si in cazul functiei ROUND