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 🙂

 

 

This entry was posted in Oracle, Oracle SQL, Oracle SQL Podstawy and tagged , , , . Bookmark the permalink.

22 Responses to Kurs Oracle SQL online. Obsługa dat

  1. Pati says:

    Kilka pytań:
    1- W przykładzie nr. 5 nie powinno być “=” ?
    2- jaką rolę spełnia “e” w przykładach 7 i 8. Po co ono jest i czemu ma służyć (być może coś mi umknęło….)
    3- czemu komenda :
    Select first_name,Trunc( Months_between(sysdate,hire_date),0)imość_dni, nvl(commission_pct, ‘zero’)
    From employees ;
    nie działa? coś jest nie tak z częścią nvl ale nie wiem co i czemu.
    Pozdrawiam

    • Rafał Stryjek says:

      Ad1. Gdybyśmy użyli “=” wybralibyśmy tylko tych, którzy zostali zatrudnieni dokładnie 9 lat temu. My chcemy wszystkich, których zostali zatrudnieni z datą >= data 9 lat temu.
      Ad2. “e” jest aliasem tabeli. Użycie aliasów umożliwia użycie e.* – wybór wszystkich kolumn z tabeli o nazwie e. Moglibyśmy również użyć:
      SELECT employees.*, Trunc(SYSDATE-hire_date) FROM employees;
      Podsumowując: alias to skrócona nazwa dla tabeli. Jeśli chcemy wyświetlić * z tabeli to musimy prefiksować ją nazwą tabeli lub aliasem.
      Ad3. Poprawione:
      Select first_name,Trunc( Months_between(sysdate,hire_date),0) ilość_dni, nvl(commission_pct, 0)
      From employees

      W NVL zero w postaci liczby powinno być. Typ danych przyjmuje z pierwszego parametru stąd spodziewał się liczby.

      Pozdrawiam

  2. Mariusz says:

    Mam pytanie odnośnie przykładów 7 i 8.
    Rozumiem, że ‘e’ jest aliasem tabeli, który to alias nadajemy we FROM. Równie dobrze może być tam inny znak. Proszę o wyjaśnienie, po co w ogóle ten alias. Dlaczego nie może być SELECT *, Trunc(SYSDATE-hire_date) FROM employees.
    Drugie pytanie – jaką rolę pełni kropka w SELECT e.*

    • Adam says:

      Dzień dobry,

      podpinam się pod pytanie Mariusza. Też nie rozumiem po co jest alias e skoro samo SELECT * daje ten sam efekt?

    • Drazo says:

      Pytanie 1
      Nie może być “SELECT *, Trunc(SYSDATE-hire_date) FROM employees” bo się nie wykona, zadziała tylko z aliasem lub nazwa tabeli, mogłoby sie wykonać tylko gdyby nie było “, Trunc(SYSDATE-hire_date) ” .
      Pytanie 2
      w SELECT e.* kropka pełni tę samą funkcję co np. w “e.hire_date” czyli oddziela nazwę tabeli lub alias od pól, które chcemy wyśwwietlić, tutaj wszystkie pola tabeli employees

  3. Artur says:

    Witam,
    funkcja sysdate zwraca mi wyłącznie datę – bez godziny. W czym problem?

    • Rafał Stryjek says:

      Witam,
      Funkcja nie wraca wyłącznie daty (bez godziny). To narzędzie, z którego Pan korzysta nakłada na datę maskę prezentującą samą godzinę. Proszę spróbować:
      select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;

  4. Agnieszka says:

    Witam.

    Mam problem, który muszę bardzo szybko rozwiązać 🙂
    Użytkownik edytuje dokument, wielokrotnie zmieniając jego status w ciągu dnia. Status dokumentu zmienia się z “w przygotowaniu” na “przygotowany” po każdym zamknięciu edycji dokumentu. Kolejny pracownik dokonuje weryfikacji, jeśli znajdzie błędy, zmienia status na “w przygotowaniu”. Operacja zazwyczaj powtarza się 3-4 razy w ciągu dnia. W związku z tym status “przygotowany” występuje kilkukrotnie w ciągu tego samego dnia.
    Interesuje mnie wyciągnięcie daty (godziny) ostatniej zmiany na “przygotowany”. Niestety kostka przygotowana przez pracownika, który się zwolnił wyrzuca mi wystąpienia statusu “przygotowany” z całego ostatniego dnia. Chciałabym, wygenerować raport tylko z jednym ostatnim wystąpieniem procesu.
    W jaki sposób mogę porównać te daty i za pomocą funkcji dopisywanej do kostki zawęzić dane na tym etapie?

    Proszę o pomoc 🙂

  5. Drazo says:

    Przykład 5
    SELECT * FROM employees
    WHERE Add_Months(hire_date,9*12)>=SYSDATE;
    tutaj dostaniemy listę wszystkich którzy zostali zatrudnieni od 9 lat temu wzwyż, nie wiem czy tak miało być jak ktoś ma date zatrudnienia z datą za miesiąc to też będzie w zestawieniu, chyba ze nie zatrudniamy z data przyszłą wtedy ok.
    można dodać warunek ( and hire_date <= sysdate )

    można też użyć between
    czyli:
    select * from employees where hire_date between add_months(sysdate,-(9*12)) and sysdate

  6. ROZMOWNA says:

    SELECT TRUNC((TO_DATE(‘15.08.2015′,’DD.MM.YYYY’),’MM’) FROM DUAL;

    Czemu to nie działa?

  7. ROZMOWNA says:

    SELECT TRUNC((TO_DATE(‘15.08.2015′,’DD.MM.YYYY’),’MM’) FROM DUAL;

  8. Szymek says:

    SELECT SYSDATE+1/24 FROM dual; pokazuję tylko datę bez godziny, co jest nie tak?

    • Rafał Stryjek says:

      Trzeba zmienić ustawienia narzędzia na format daty z godziną, np.: ‘YYYY/MM/DD HH24:MI:SS’

  9. Szymek says:

    również nie rozumiem dlaczego w 7 i 8 przykładzie należy dodać alias tj.:
    SELECT e.*, Trunc(SYSDATE-hire_date) FROM employees e;
    czemu bez dodanie e.* i employees e nie działa zapytanie?

    • Rafał Stryjek says:

      W Oracle jeśli chcemy wyświetlić * z tabeli plus coś jeszcze w dodatkowej/dodatkowych kolumnach – np. sysdate, wynik działania funkcji – trzeba wskazać skąd ta gwiazdka “pochodzi”. Jest to nieintuicyjne, bo ten wymóg obowiązuje nawet jeśli jest tylko jedna tabela w zapytaniu i wydaje się, że baza powinna “wiedzieć” skąd ma wziąć gwiazdkę.

      Zatem nie piszemy tak: select *,sysdate from employees; Musimy napisać tak: select employees.*, sysdate from employees; lub użyć aliasu dla tabeli: select e.*, sysdate from employees e;

      Jako ciekawostkę: w bazie MS SQL Server powyższego wymogu nie ma. Nie trzeba wskazywać skąd “pochodzi” gwiazdka.

      • Szymek says:

        więc na jakiej zasadzie wyciąga się wtedy sysdate? rozumiem, że funkcja sysdate jest wszędzie? – zapytanie select sysdate from employees również zwraca datę, więc wnioskuję że tak.

  10. Rysiek says:

    Witam
    Czy znacie jakąś sprytną sztuczkę aby obliczyć ilość dni roboczych w miesiącu?
    Z góry dzięki za pomoc
    Rysiek

    • kosma says:

      NIe ma na to dedykowanych funkcji w Oracle, gdyż baza “nie wie”, które dni są w PL wolne od pracy. Przy uproszczonym założeniu, że jest to sobota i niedziela, można posłużyć się funkcją to_char(sysdate, ‘D’), która zwraca numer dnia w tygodniu (1-7) i wykluczyć dni weekendowe

      Zwykle stosuje się takie rozwiązanie:
      1) tworzymy tabelę, w której zaznaczymy dni wolne od pracy

      CREATE TABLE “CALENDAR”
      ( “CAL_ID” NUMBER NOT NULL ENABLE,
      “CAL_DATE” DATE,
      “CAL_YEAR” NUMBER(4,0) NOT NULL ENABLE,
      “CAL_MONTH” NUMBER(2,0) NOT NULL ENABLE,
      “CAL_DAY” NUMBER(2,0) NOT NULL ENABLE,
      “DAY_OF_WEEK” NUMBER(2,0) NOT NULL ENABLE,
      “WEEK_OF_YEAR” NUMBER(2,0) NOT NULL ENABLE,
      “IS_HOLIDAY” NUMBER(1,0) NOT NULL ENABLE,
      “DAY_NAME” VARCHAR2(10),
      CONSTRAINT “PK_CAL_ID” PRIMARY KEY (“CAL_ID”) ENABLE
      );

      2) zapytaniem SQL zwracamy ilość dni roboczych w miesiącu

      SELECT count(*)
      FROM CALENDAR
      WHERE cal_month = 11
      AND cal_year = 2016
      AND IS_HOLIDAY = 0

  11. Paweł says:

    Do przykładu nr 5 zanim sprawdziłem jak być powinno wg. kursu zapisałem to w taki sposób:
    SELECT * FROM employees
    WHERE HIRE_DATE > add_months(sysdate, -108);

    I zwróciło takie same wyniki 🙂

  12. Marcin says:

    a czy daty można ze sobą porównywać ?
    np. data1 > data2 ?

    • Przemysław Kantyka says:

      Jak najbardziej można użyć operatorów < , <=, =, >=, <> oraz np. between

Leave a Reply to Pati Cancel reply

Your email address will not be published.