Witajcie po małej przerwie 🙂 W tej lekcji pokażemy coś co jest istotne chyba w każdym projekcie – poprawna obsługa dat. Pokażemy jak dodawać do daty dni, miesiące i lata, pokażemy jak wyznaczać różnicę między dwoma datami. Poznasz funkcje wykorzystywane do przetwarzania dat.
Przykład 1. Wyświetl aktualną datę.
SELECT SYSDATE FROM dual;
Do pobrania aktualnej daty służy funkcja sysdate – zwraca wartość typu date.
Przykład 2. Wyświetl datę jaka będzie za 100 dni
SELECT SYSDATE+100 FROM dual;
Dodanie liczby do daty (lub odjęcie liczby od daty) powoduje jej przesunięcie o taka liczbę dni.
Zatem jak dodać 1 godzinę do aktualnej daty?
Przykład3. Wyświetl datę (i czas) jaka będzie za godzinę.
SELECT SYSDATE+1/24 FROM dual;
Jak pewnie zauważyliście w Oracle typ date przechowuje datę i czas z dokładnością 1 sekundy.
Jak zatem dodać 1 miesiąc? Jak wiadomo miesiące są różnej długości: 31 dni, 30 dni, ale również 28 dni a nawet 29 dni. W tym celu należy użyć funkcji add_months.
add_months (data, liczba_miesięcy)
Funkcja add_months służy do dodawania do daty liczby miesięcy.
Przykład 4. Wyświetl datę jaka będzie za 10 miesięcy
SELECT Add_Months(SYSDATE,10) FROM dual;
Przykład 5. Wyświetl pracowników (employees) zatrudnionych w ciągu ostatnich 9 lat
SELECT * FROM employees WHERE Add_Months(hire_date,9*12)>=SYSDATE;
Z racji tego, że nie ma funkcji add_years do dodawania lat należy korzystać z funkcji add_months. Jak w takim razie odejmować miesiące (oraz lata)?
Przykład 6. Wyświetl datę jaka była 6 miesięcy temu.
SELECT Add_Months(SYSDATE,-6) FROM dual;
Wiemy już jak dodawać miesiące oraz lata. Wiemy też jak dodawać i odejmować dni oraz części dnia.
Jak w takim razie wyznaczać różnicę między dwoma datami? O tym niżej.
Data1 – Data2 = liczba_dni
Przykład 7. Dla każdego pracownika wyświetl od ilu pełnych dni jest zatrudniony (użyj funkcji trunc).
SELECT e.*, Trunc(SYSDATE-hire_date) FROM employees e;
W celu wyznaczenia różnicy w miesiącach (lub latach) można skorzystać z funkcji months_between. Funkcja months_between zwraca różnicę w miesiącach między dwoma datami.
Przykład 8. Dla każdego pracownika wyświetl od ilu pełnych miesięcy jest zatrudniony (użyj funkcji trunc).
SELECT e.*, Trunc(Months_Between(SYSDATE, hire_date)) FROM employees e;
W celu uzyskania liczby lat należy wartość zwróconą przez months_between podzielić na 12.
Przydatne funkcje:
Last_Day(data) – funkcja zwraca ostatni dzień miesiąca przekazanego jako parametr.
Przykład 9. Wyświetl ostatni dzień bieżącego miesiąca
SELECT Last_Day(SYSDATE) FROM dual;
Next_Day(data1, dzień_tygodnia) – wyświetla najbliższy dzień_tygodnia szukając od data1
Przykład 10. Wyświetl najbliższy wtorek
SELECT next_day(SYSDATE,'wtorek') FROM dual;
To_Char (data [,format]) – funkcja rzutuje datę na typ znakowy. Pozwala na tekstowo reprezentację daty lub/i wybranie takich wielkości jak np. kwartał, dzień w roku. Co może być formatem?
• D – numer dnia w tygodniu
• DD – numer dnia w miesiącu
• DDD – numer dnia w roku
• MM – numer miesiąca
• YYYY – numer roku
• HH – godzina dnia (od 1 do 12)
• Q – kwartał
• MI – minuty
• SS – sekundy
• W – tydzień w miesiącu
• WW – tydzień w roku
• MON – trzyliterowy skrót miesiąca
• MONTH – nazwa miesiąca
• DAY – nazwa dnia
• DY – skrót nazwy dnia
• RM – miesiąc w postaci rzymskiej
• YEAR – rok słownie
• DL – data w formacie długim
Przykład 11. Dla każdego z pracowników wyświetl, w którym kwartale został zatrudniony
SELECT e.*, To_Char(hire_date,'q') kwartał FROM employees e;
Przykład 12. Dla bieżącej daty wyświetl – rok w formacie (liczbowo), dzień tygodnia słownie, dzień miesiąca słownie
SELECT To_Char(SYSDATE,'yyyy day month') FROM dual;
Jak widać maski można ze sobą łączyć.
To_Date(wartość rzutowana, format) – funkcja rzutująca na typ date
Przykład 13. Wyświetl ostatni dzień miesiąca dla daty 01.02.2004
SELECT Last_Day(To_Date('01.02.2004','dd.mm.yyyy')) FROM dual;
TRUNC(data, dokładność) – obcina datę zgodnie z przekazaną dokładnością
ROUND(data, dokładność) – zaokrągla datę zgodnie z przekazaną dokładnością
Przykład 14. Wybierz pierwszy dzień roku oraz pierwszy dzień miesiąca dla bieżącej daty
SELECT Trunc(SYSDATE,'yyyy'),Trunc(SYSDATE,'mm') FROM dual;
Jak widać funkcja trunc działa nie tylko na liczbach, ale również na datach. Możecie użyć funkcji Round, która nie przytnie a zaokrągli (po połowie roku zaokrągli “w górę” do kolejnego).
Co w następnej lekcji?
Agregacje – czyli zajmiemy się funkcjami agregującymi 🙂
