Kurs Oracle SQL online. Obsługa dat

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 🙂

 

 

About Rafal Stryjek

My LinkedIn profile. Passionate about data. Data Warehousing Expert. Snowflake Data SuperHero & Oracle ACE Associate. International conferences Speaker: Snowflake Build Summit-BUILD.local 2021, Oracle Open World, DOAG, UKOUG, APEX World,.. Visit dataconsulting.pl website!
This entry was posted in Oracle, Oracle SQL, Oracle SQL Podstawy and tagged , , , . Bookmark the permalink.

Leave a Reply