Data: 29.01.2023

SQL Oracle funkcje daty i czasu

Spis treści

• SQL Oracle funkcje daty i czasu
  • Podstawowe typy danych daty i czasu
  • Sposób zapisu daty w Oracle
  • Zwracanie aktualnej daty – CURRENT_DATE
  • Zwracanie aktualnej daty – SYSDATE
  • Odczytywanie roku z daty –  EXTRACT(YEAR FROM Date)
  • Odczytywanie miesiąca z daty – EXTRACT(MONTH FROM Date)
  • Odczytywanie dnia z daty – EXTRACT(DAY FROM Date)
  • Wyświetlanie ostatniego dnia miesiąca – LAST_DAY()
  • Wyliczanie różnicy w miesiącach pomiędzy datami – MONTHS_BETWEEN()
  • Wyliczanie różnicy w dniach pomiędzy datami
  • Dodawanie miesięcy do daty – ADD_MONTHS()
  • Dodawanie lat do daty
  • Dodawanie dni do daty i odejmowanie dni
  • Wyświetlanie nazwy dnia tygodnia
  • Wyświetlanie kwartału dla daty
  • Konwertowanie znaków na datę – TO_DATE()
  • „Obcinanie” dat – TRUNC()

W artykule przedstawiam najczęściej używane funkcje daty i czasu dla bazy danych Oracle. Funkcji daty i czasu używamy do wykonywania działań na datach i godzinach.

Przykłady i zadania prezentowane w artykule możesz uruchomić korzystając ze schematu bazy Human Resources.

Podstawowe typy danych daty i czasu

W Oracle mamy do dyspozycji następujące typy danych daty i godziny:

Sposób zapisu daty w Oracle

Daty zapisujemy w apostrofach. Najlepiej zapisywać je ciągiem tekstowym bez separatorów np. '20211201′ (’yyyyMMdd’), gdyż jest to format neutralny językowo, niezależny od ustawień regionalnych.

Poniżej przedstawiam funkcje, za pomocą których możesz wykonywać operacje na datach i godzinach w bazach Oracle.

Zwracanie aktualnej daty – CURRENT_DATE

Funkcja CURRENT_DATE zwraca aktualną datę w strefie czasowej sesji. Jest to data systemowa. Nie podajemy w niej argumentów.

Przykład:

SELECT CURRENT_DATE
FROM Dual;

Wynik zapytania: 2023-01-29

W przykładzie wyświetlamy datę systemową. Podajemy nazwę tabeli Dual.

W Oracle nie możemy utworzyć zapytania bez klauzuli FROM. Tabela Dual została utworzona, aby można się było odwołać do niej w zapytaniach, w których nie potrzebujemy konkretnych danych z konkretnej tabeli. Zawiera ona jeden wiersz i jedną kolumnę.

Zwracanie aktualnej daty – SYSDATE

Za pomocą instrukcji SYSDATE możemy wyświetlić bieżącą datę i godzinę ustawione dla systemu operacyjnego na komputerze, z którego łączymy się z serwerem.

Przykład:

W przykładzie za pomocą instrukcji SYSDATE wyświetlamy datę w formacie rok, miesiąc i dzień.

SELECT SYSDATE
FROM DUAL;

Wynik zapytania: 2023-01-29

Przykład:

Otrzymujemy aktualny czas i datę systemu operacyjnego:

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') as Teraz
FROM DUAL;

Wynik zapytania: 01-29-2023 13:07:06

SYSDATE wyświetla bieżącą datę z serwera, na którym znajduje się baza danych. CURRENT_DATE wyświetla bieżącą datę z komputera użytkownika, z którego łączymy się z serwerem.

Odczytywanie roku z daty –  EXTRACT(YEAR FROM Date)

Funkcja EXTRACT(YEAR FROM Date) zwraca rok z przekazanej jako argument daty.

Składnia:

EXTRACT(YEAR FROM date) RETURNS integer

Argumenty:

date – podajemy datę, dla której chcemy otrzymać rok

Przykład:

W przykładzie za pomocą funkcji EXTRACT(YEAR FROM HIRE_DATE) zwracamy rok z daty zatrudnienia pracownika (kolumna Hire_Date) i wybieramy tylko tych pracowników, którzy zostali zatrudnieni w 2007 roku.

SELECT HIRE_DATE, EXTRACT(YEAR FROM HIRE_DATE) as Rok
FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 2007;

Wynik zapytania:

Odczytywanie miesiąca z daty – EXTRACT(MONTH FROM Date)

Funkcja EXTRACT(MONTH FROM Date) zwraca numer miesiąca z przekazanej jako parametr daty.

Składnia:

EXTRACT(MONTH FROM date) RETURNS integer

Argumenty:

date – podajemy datę, dla której chcemy otrzymać miesiąc

Przykład:

W przykładzie za pomocą funkcji EXTRACT(MONTH FROM HIRE_DATE) zwracamy miesiąc z daty zatrudnienia pracownika (kolumna Hire_Date) i wybieramy tylko tych pracowników, którzy zostali zatrudnieni w maju.

SELECT HIRE_DATE, EXTRACT(MONTH FROM HIRE_DATE)
FROM EMPLOYEES
WHERE EXTRACT(MONTH FROM HIRE_DATE) = 5;

Wynik zapytania:

Odczytywanie dnia z daty – EXTRACT(DAY FROM Date)

Funkcja EXTRACT(DAY FROM Date) zwraca numer dnia przekazanej jako argument daty.

Składnia:

EXTRACT(DAY FROM date) RETURNS integer

Argumenty:

date – podajemy datę, dla której chcemy otrzymać dzień

Przykład:

W przykładzie za pomocą funkcji EXTRACT(DAY FROM HIRE_DATE) zwracamy dzień z daty zatrudnienia pracownika (kolumna Hire_Date) i wybieramy tylko tych pracowników, którzy zostali zatrudnieni 14-go dnia miesiąca.

SELECT HIRE_DATE, EXTRACT(DAY FROM HIRE_DATE)
FROM EMPLOYEES
WHERE EXTRACT(DAY FROM HIRE_DATE) = 14;

Wynik zapytania:

Wyświetlanie ostatniego dnia miesiąca – LAST_DAY()

Funkcja LAST_DAY() zwraca ostatni dzień miesiąca (ang. last day) dla określonej daty.

Składnia:

LAST_DAY(date)

Argumenty:

date – podajemy datę, dla której chcemy otrzymać ostatni dzień miesiąca, do którego ta data należy

Przykład:

W przykładzie za pomocą funkcji LAST_DAY() wyświetlamy ostatni dzień dla daty zatrudnienia pracownika.

SELECT HIRE_DATE, LAST_DAY(HIRE_DATE)
FROM EMPLOYEES;

Wynik zapytania:

Wyliczanie różnicy w miesiącach pomiędzy datami – MONTHS_BETWEEN()

Funkcja MONTHS_BETWEEN() oblicza różnicę w miesiącach pomiędzy dwiema datami

Składnia:

MONTHS_BETWEEN(date1, date2)

Argumenty:

date1 i date2 – podajemy daty, pomiędzy którymi ma być wyliczona różnica.

Przykład

SELECT HIRE_DATE, CURRENT_DATE, MONTHS_BETWEEN(CURRENT_DATE,HIRE_DATE)
FROM EMPLOYEES;

W przykładzie wyliczamy różnicę w miesiącach pomiędzy datą zatrudnienia pracownika a datą dzisiejszą.

Wynik zapytania:

Wyniki działania funkcji możemy zaokrąglać. Za pomocą funkcji FLOOR() zaokrąglimy wynik do liczb całkowitych (int).

SELECT HIRE_DATE, CURRENT_DATE, FLOOR(MONTHS_BETWEEN(CURRENT_DATE,HIRE_DATE))
FROM EMPLOYEES;

Wynik zapytania:

Wyliczanie różnicy w dniach pomiędzy datami

Aby wyliczyć różnicę w dniach pomiędzy dwiema datami odejmujemy je od siebie.

Przykład:

Wyliczamy liczbę dni pomiędzy datą rozpoczęcia i datą zakończenia pracy przez pracownika.

SELECT EMPLOYEE_ID, START_DATE, END_DATE, END_DATE-START_DATE
FROM JOB_HISTORY;

Wynik zapytania:

Dodawanie miesięcy do daty – ADD_MONTHS()

Za pomocą funkcji ADD_MONTHS() możemy dodać do danej daty określoną ilość miesięcy.

Składnia:

ADD_MONTHS (date, number_months) RETURNS date

Argumenty funkcji:

date – w argumencie podajemy datę, do której chcemy dodać określoną liczbę miesięcy

number_months – podajemy ilość miesięcy, którą chcemy dodać do daty

Przykład

Do daty zatrudnienia (Hire_Date) dodajemy 1 miesiąc.

SELECT HIRE_DATE, ADD_MONTHS(HIRE_DATE,1)
FROM EMPLOYEES;

Wynik zapytania:

Przykład

Wyświetlamy jaka była data dwa miesiące temu. Dwa miesiące odejmujemy od daty bieżącej t. 29 stycznia 2023 zwracanej przez SYSDATE.

SELECT ADD_MONTHS(SYSDATE,-2)
FROM dual;

Wynik zapytania: 2022-11-29

Przykład

Wyświetlamy dane pracowników, którzy zostali zatrudnieni w ciągu ostatnich 15 lat.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE ADD_MONTHS(HIRE_DATE,15*12)>=SYSDATE;

Wynik zapytania:

Dodawanie lat do daty

W Oracle SQL nie ma specjalnej funkcji do dodawania roku do dat. Możemy użyć funkcji ADD_MONTHS() i dodać 12 miesięcy.

Przykład:

Do daty 2022-07-11 dodajemy jeden rok.

SELECT '2022-07-11', ADD_MONTHS('20220711', 12)
FROM DUAL;

Wynik zapytania: 2023-07-11

Przykład

Do daty 2022-07-11 dodajemy trzy lata.

SELECT '2022-07-11', ADD_MONTHS('20220711', 36)
FROM DUAL;

Wynik zapytania: 2025-07-11

Warto zwrócić uwagę na działanie funkcji ADD_MONTHS() w kontekście lat przestępnych.

Funkcja ADD_MONTHS() zwraca ostatni dzień wynikowego miesiąca, jeśli jako argument funkcji  podamy ostatni dzień miesiąca.

Przykład:

Jako argument podajemy datę 28 lutego 2016 i dodajemy do niej 12 miesięcy. W wyniku otrzymamy 28 lutego 2017 roku.

SELECT '2016-02-28', ADD_MONTHS('20160228', 12)
FROM DUAL;

Wynik zapytania: 2017-02-28

Jeśli rok będzie przestępny, w wyniku zapytania otrzymamy 29 lutego kolejnego roku. W przykładzie do daty 28 lutego 2015 roku dodajemy jeden rok.

SELECT '2015-02-28', ADD_MONTHS('20150228', 12)
FROM DUAL;

Wynik zapytania: 2016-02-29

Dodawanie dni do daty i odejmowanie dni

Aby powiększyć datę o określoną ilość dni dodajemy do niej dni za pomocą znaku plus (+).

Przykład

W przykładzie:

• wybieramy tylko rekordy, które mają ID mniejsze od 115
• do daty z kolumny Hire_Date dodajemy jeden dzień.

SELECT EMPLOYEE_ID, HIRE_DATE, HIRE_DATE+1
FROM EMPLOYEES
WHERE EMPLOYEE_ID <115;

Wynik zapytania:

Dni od daty odejmujemy za pomocą znaku minus (-).

SELECT EMPLOYEE_ID, HIRE_DATE, HIRE_DATE-1
FROM EMPLOYEES
WHERE EMPLOYEE_ID <115;

Wynik zapytania:

Wyświetlanie nazwy dnia tygodnia

Przetwarzanie dat możliwe jest również za pomocą funkcji konwersji.

Nazwę dnia tygodnia możemy uzyskać konwertując datę na tekst.

Przykład

SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'Day')
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Wtorek      ';

W przykładzie uzyskujemy nazwę dnia dla daty zatrudnienia pracownika i wybieramy tylko tych pracowników, którzy zatrudnieni zostali we wtorek.

Wynik zapytania:

Za pomocą funkcji TO_CHAR() tworzymy dodatkową kolumnę tekstową. Ilość znaków w kolumnie domyślnie dostosowana została do najdłuższego tekstu, który ma się w niej zmieścić. W tym przypadku jest to „poniedziałek”. Nazwy krótszych dni tygodnia zostały dopełnione z prawej strony spacjami. Dlatego wpisując w klauzuli 'WHERE’ nazwę inną niż poniedziałek należy dopełnić ją spacjami w zapytaniu tak samo jak jest wyniku zapytania.

Możemy uzyskać poniższy format daty:

• ’DAY’ zwraca nazwę dnia wielkimi literami np. 'SOBOTA’
• ’day’ zwraca nazwę dnia małymi literami np. 'sobota’
• ’DY’ zwraca trzy pierwsze litery nazwy dnia tygodnia wielkimi literami np. 'SOB’
• ’Dy’ zwraca trzy pierwsze litery nazwy dnia tygodnia małymi literami – pierwsza litera wielka np.’Sob’
• ’dy’ zwraca trzy pierwsze litery nazwy dnia tygodnia małymi literami np. 'sob’

Możemy wyodrębnić nazwę dnia w innym języku. Aby to zrobić, musimy użyć trzeciego parametru, NLS_DATE_LANGUAGE. Ten parametr może przyjmować dowolną prawidłową nazwę języka.

Przykład

W poniższym przykładzie funkcja zwraca nazwę po hiszpańsku.

SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'Day','NLS_DATE_LANGUAGE = Spanish')
FROM EMPLOYEES;

Wynik zapytania:

Uwaga! W Oracle rozróżniane są małe i wielkie litery. Jeśli tekst w bazie danych jest napisany wielkimi literami, musimy w zapytaniu również napisać go wielkimi literami.

Wyświetlanie kwartału dla daty

Aby wyświetlić kwartał, do którego należy data dokonujemy konwersji daty na typ tekstowy.

Przykład

SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'Q')
FROM EMPLOYEES;

Wynik zapytania:

Konwertowanie znaków na datę – TO_DATE()

Za pomocą funkcji TO_DATE() możemy przekonwertować znak na typ danych data.

Składnia:

TO_DATE (string, format_mask, nls_language)

Argumenty:

string: podajemy ciąg znaków do przekonwertowania.

format_mask: opcjonalny parametr, którego używamy do określenia formatu używanego do konwersji.

nls_language: opcjonalny parametr , którego używamy do określenia języka nls, który ma być używany do konwersji.

NLS to parametr bazy danych, za pomocą którego możemy ustawić język w bazie, w którym wyświetlane będą np. komunikaty o błędach, data i godzina, konwencje monetarne i kalendarzowe.

Przykład

W przykładzie konwertujemy ciąg tekstowy na datę.

SELECT TO_DATE('20220321','YYYY-MM-DD')
FROM DUAL;

Wynik zapytania: 2022-03-21

„Obcinanie” dat – TRUNC()

Funkcji TRUNC() używamy, aby uzyskać datę obciętą do określonej jednostki miary np. do roku.

Składnia:

TRUNC (date, format)

Argumenty:

date: podajemy datę, którą będziemy modyfikować.

format: jest to opcjonalny parametr, który służy do określenia jednostki miary używanej do obcinania.

Przykład:

W przykładzie funkcja TRUNC() zwraca pierwszy dzień roku dla daty 23 stycznia 2022 roku.

SELECT TRUNC( TO_DATE ('22-STY-23'),'YEAR')
FROM DUAL;

Wynik zapytania: 2022-01-01

Przykład

Dla każdego pracownika wyświetlamy przez ile pełnych lat był zatrudniony.

SELECT EMPLOYEE_ID, START_DATE, END_DATE,
  TRUNC(MONTHS_BETWEEN(END_DATE,START_DATE)/12)
FROM JOB_HISTORY;

Wynik zapytania:

Informacje na temat funkcji daty i czasu w MS SQL Server znajdziesz w artykule MS SQL Server funkcje daty i czasu

Paula Gajewska
Programistka Python, SQL

Udostępnij wpis: