Jak filtrować, przekształcać, dodawać i odejmować punkty w czasie w zapytaniach SQL? – lekcja 18

Wstęp

Punkt w czasie w zapytaniach SQL reprezentowany jest jako specyficznie sformatowany łańcuch znaków. O tym jakimi narzędziami przekształcić zrozumiały dla człowieka napis w akceptowaną dla motoru bazy danych wartość oraz o tym jak sformatować otrzymaną z bazy danych wartość w przyjazny dla człowieka napis jest niniejsza lekcja.

Najprościej

Najprostsze wyszukiwanie wg daty to przyrównanie literału do kolumny o datoczasowym typie. Skąd wiedzieć jak formatować literał aby Oracle zaakceptował go jako datę? Domyślny format odczytasz z parametrów Twojej sesji. Parametry sesji jawnie lub nie ustawiane są przez Twojego klienta.

SELECT
  *
FROM
  nls_session_parameters
WHERE
  parameter like 'NLS_DATE%';

Parametr NLS_DATE_FORMAT wskazuje, że format daty obowiązujący dla mojej sesji to dzień określony dwoma cyframi. Miesiąc wskazany trzyliterowym skrótem składającym się z dużych liter. I rok wskazany dwoma cyframi.

Parametr NLS_DATE_LANGUAGE wskazuje, że Oracle oczekuje nazwy miesiąca w języku angielskim. Czyli takie zapytanie wybierze z bazy danych nazwy obiektów utworzonych po 20 maja 1972 roku.

SELECT
  object_name
FROM
  all_objects
WHERE
  created>'20-MAY-72';

Jeśli Oracle nie poradzi sobie z niejawną konwersją literału do typu datoczasowego dostaniesz błąd.

Dla zrzutów ekranu używałem Oracle 19c dostępnego w przeglądarce internetowej na Oracle Live SQL.

Typy datoczasowe

Do obsługi punktu w czasie wykorzystywane są dwa typy o różnej dokładności i zakresie. Oba obsługują dane składające się zarówno z daty jak i czasu.

• jeden to typ date o dokładności jednej sekundy
• drugi to timestamp o dokładności nanosekundy

O zakresach będę mówił w kursie o projektowaniu bazy danych. W komentarzach daj wyraz swojej potrzebie zaliczenia takiego kursu. Typ timestamp może przechowywać strefę czasową. Wtedy nazywa się timestamp with time zone lub timestamp with local time zone.

W innych motorach baz danych możesz spotkać się z osobnym typem dla daty i osobnym dla czasu.

Komponent czasowy

W Oracle punkt w czasie wskazany z pominięciem części czasowej zostanie ustawiony na północ. Czyli w moim przykładzie będzie to północ dwudziestego maja 1972 roku.

'20-MAY-72' = '20-MAY-1972 00:00:00'

Warto o tym pamiętać.

Zwróć uwagę, że ustawienie formatu daty dla mojej sesji nie zawiera części czasowej. A Ty wiesz, że dane zawierają komponent czasowy. Sprawdź co się stanie gdy do literału dopiszesz część czasową.

SELECT
  object_name
FROM
  all_objects
WHERE
  created>'20-MAY-72 12:13:14';

Pełną listę elementów formatu daty znajdziesz w dokumentacji.

Modyfikacja domyślnego formatu

Modyfikując parametr NLS_DATE_FORMAT dla swojej sesji mówisz Oracle w jaki sposób ma dokonać niejawnej konwersji literału do typu date.

ALTER SESSION
  SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

Teraz będzie oczekiwał czterocyfrowego roku, dwucyfrowego miesiąca i dwucyfrowego dnia poodzielanych myślnikami. Będzie także oczekiwał części czasowej w formacie dwudziestoczterogodzinnym, dwucyfrowych minut i dwucyfrowych sekund poodzielanych dwukropkami. Ustaw parametr swojej sesji i ponów zapytanie.

SELECT
  object_name
FROM
  all_objects
WHERE
  created>'1972-MAY-20 12:13:14';

Format możesz ubogacić własnymi treściami. Pod warunkiem, że użyjesz cudzysłowu.

ALTER SESSION
  SET NLS_DATE_FORMAT=' "Data Marcina:" yyyy-mm-dd hh24:mi:ss';

Format typu timestamp ustawiany jest według identycznych zasad osobnymi parametrami sesji.

SELECT
  *
FROM
  nls_session_parameters
WHERE
  parameter like 'NLS_TIMESTAMP%';

Pamiętaj, że ustawienia formatu daty jedynie instruują motor jak ma interpretować przekazywane mu literały oraz formatować zwracane przez niego dane. W bazie danych jest to zawsze ten sam ciąg bajtów.

ANSI konwersja literałów do typów datoczasowych

Jawnej konwersji literału do typu datoczasowego dokonasz wykorzystując słowa kluczowe DATE i TIMESTAMP ze standardu ANSI języka SQL.

DATE konwertuje literały w formacie czterocyfrowy rok, myślnik, dwucyfrowy miesiąc, myślnik, dwucyfrowy dzień do typu date.

Zwróć uwagę, że nie możesz podać części czasowej. Pamiętaj, że w bazie danych Oracle część czasowa zapisana jest zawsze. Dlatego gdy chcesz wyszukać rekordy z danego dnia używaj przyrównania jedynie wtedy gdy masz pewność, że w bazie danych część czasowa jest wyzerowana.

SELECT
  object_name
FROM
  all_objects
WHERE
  created=DATE'2022-05-20';

W przypadkach gdy część czasowa danych jest różna od północy wskazuj zakres.

SELECT
  object_name
FROM
  all_objects
WHERE
  created>=DATE'2022-05-20'
AND created<DATE'2022-05-21';

Zwróci nazwy obiektów utworzonych dwudziestego maja 2020 roku.

TIMESTAMP konwertuje literały w formacie czterocyfrowy rok, myślnik, dwucyfrowy miesiąc, myślnik, dwucyfrowy dzień, spacja, godzina w formacie dwudziestoczterogodzinnym, dwukropek, minuty, dwukropek, sekundy, kropka, dziewięć cyfr części ułamkowej do typu timestamp.

Możesz wykorzystać do przyrównania części czasowej dla kolumn typu timestamp jak i date. W przypadku kolumn typu date Oracle dokona niejawnej konwersji. Trzeba pamiętać, że wtedy obetnie część ułamkową sekundy oraz strefę czasową. Jeśli nie interesują Cię dane z całego dnia, a tylko z określonej godziny wtedy wykorzystaj słowo kluczowe TIMESTAMP:

SELECT
  object_name
FROM
  all_objects
WHERE
  timestamp>=TIMESTAMP'2022-05-20 13:00:00'
AND timestamp<TIMESTAMP'2022-05-20 14:00:00';

Może nie zwrócić żadnych danych. Teraz samodzielnie dostosuj warunki aby zapytania zwróciły dane.

Natywna konwersja literałów do typów datoczasowych

Natywne funkcje konwertujące literały do typów date i timestamp to odpowiednio to_date i to_timestamp. Każda z tych funkcji przyjmuje trzy parametry.

• pierwszy z nich to punkt w czasie w postaci literału, który ma być zamieniony na dane typu datoczasowego
• drugi parametr to format mówiący motorowi bazy danych jak ma interpretować literał
• trzeci to ustawienia narodowe według których interpretować literał

Jeśli pominiesz drugi i trzeci to Oracle użyje parametrów sesji.

Warto zwrócić uwagę, że w odróżnieniu od sztywnej konwersji narzędziami ANSI natywne funkcje konwertujące wybaczają literówki.

ALTER SESSION
  SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

SELECT
  to_date('20/05-2022 13:24.55', 'DD-MM-YYYY HH24:MI:SS')
  ,to_timestamp('20-05/2022 13:24,55.1234', 'DD-MM-YYYY HH24:MI:SS.FF')
FROM
  dual;

Mimo literówek – użyłem łamańca zamiast myślnika w dacie oraz kropki i przecinka zamiast dwukropka w czasie – Oracle skonwertował literał bezbłędnie.

Może się zdarzyć, że zamiast numeru miesiąca czy dnia tygodnia dysponujesz jego nazwą. Niezależnie od języka w którym ta nazwa występuje Oracle potrafi skonwertować taki literał na dane typu datoczasowego.

Trzecim parametrem mówisz mu w jakim języku otrzyma nazwę. Gdy tego nie zrobisz weźmie tę informację z ustawień sesji.

ALTER SESSION
  SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

SELECT
   to_date('maj','MONTH','NLS_DATE_LANGUAGE=Polish')
  ,to_date('cze','MON','NLS_DATE_LANGUAGE=Polish')
  ,to_timestamp('wtorek','day','NLS_DATE_LANGUAGE=Polish') -- użyj bieżącego dnia tygodnia
  ,to_timestamp('wt','dy','NLS_DATE_LANGUAGE=Polish') -- użyj bieżącego dnia tygodnia
FROM
  dual;

Możesz używać skrótów nazw.

Funkcje zwracające aktualny czas

Udostępniane przez Oracle funkcje zwracające aktualny czas różnią się zwracanym typem, wrażliwością na strefę czasową i źródłem czasu.

• current_date zwraca typ date w strefie czasowej sesji. Czyli to co ma ustawione klient.
• current_timestamp zwraca timestamp with time zone w strefie czasowej sesji. Czyli według ustawień klienta.
• localtimestamp zwraca timestamp bez strefy czasowej, ale z jej uwzględnieniem. Czyli to co jest ustawione na kliencie.
• sysdate zwraca typ date według ustawień hosta na którym pracuje instancja bazy danych
• systimestamp zwraca timestamp with time zone według ustawień hosta na którym pracuje instancja bazy danych.

ALTER SESSION
  SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

SELECT
  current_date "czas sesji"
  ,current_timestamp "stempel sesji ze strefą"
  ,localtimestamp "stempel sesji bez strefy"
  ,sysdate "czas hosta"
  ,systimestamp "stempel hosta ze strefą"
FROM
  dual;

Formatowanie danych datoczasowych

Do jawnego przekształcenia danych datoczasowych w przyjazny człowiekowi napis służy przyjmująca trzy argumenty funkcja to_char.

• pierwszym jest wartość datoczasowa
• drugim docelowy format napisu
• trzeci ustawia język w jakim mają być prezentowane nazwy dni tygodnia lub miesięcy

SELECT
  to_char(DATE'1972-05-20','day','NLS_DATE_LANGUAGE=Polish') "dzień tygodnia"
  ,to_char(DATE'1972-05-20','DL','NLS_DATE_LANGUAGE=Polish') "długi format daty"
FROM
  dual;

Ja urodziłem się w majową sobotę. Sprawdź w jaki dzień tygodnia przypadły Twoje narodziny.

Zauważ, że funkcja to_char może zwrócić jedynie potrzebny fragment daty. Sprawdź co się stanie gdy argument 'day’ napiszesz dużymi literami. Albo jedynie litera 'd’ będzie duża.

Na wzór mojego napisz zapytanie, które zwróci miesiąc Twoich urodzin w języku francuskim.

Do wycinania fragmentów daty służą dwie dedykowane funkcje. Obie zwracają typ datoczasowy.

• trunc domyślnie zwraca datę z pierwszego argumentu obciętą z dokładnością dnia. Czyli część czasowa ustawiona jest na północ. Drugim argumentem możesz wskazać inną część daty.
• round zwraca datę z pierwszego argumentu zaokrągloną do najbliższego dnia. Chyba, że drugim argumentem zaznaczysz inne zaokrąglenie.

ALTER SESSION
  SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

SELECT
  trunc(DATE'2021-11-12', 'YEAR') "data obcięta do roku"
  ,round(DATE'2021-11-12', 'YEAR') "data zaokrąglona do roku"
  ,trunc(TIMESTAMP'2021-11-12 13:44:15', 'HH') "data obcięta do godziny"
  ,round(TIMESTAMP'2021-11-12 13:44:15', 'HH') "data zaokrąglona do godziny"
FROM
  dual;

Wszystkie możliwe modele formatu znajdziesz w dokumentacji.

Arytmetyka datoczasowa

Oracle przechowuje dane typu date jako liczbę dni. Dlatego przesuniesz datę w czasie dodając lub odejmując odpowiednią liczbę.

SELECT
  sysdate-1 "ta sama godzina wczoraj"
  ,sysdate+1 "ta sama godzina jutro"
FROM
  dual;

Gdy od daty odejmiesz datę otrzymasz liczbę oznaczającą liczbę dzielących je dni.

SELECT
  sysdate-DATE'1972-05-20' "liczba dni od narodzin Marcina"
FROM
  dual;

Miesiące mają różną liczbę dni, więc arytmetyka jest bardziej skomplikowana. W sukurs przychodzą dedykowane miesiącom funkcje:

• add_months dodaje określoną w argumencie drugim liczbę miesięcy do daty z pierwszego argumentu. Zwraca typ date.
• months_between odejmuje datę z argumentu drugiego od daty z argumentu pierwszego. Zwraca liczbę miesięcy.

Część ułamkowa obliczana jest przy założeniu, że miesiąc ma 31 dni.

SELECT
  to_char(add_months(date'1972-05-20',-9), 'yyyy-mm-dd') "poczęcie Marcina"
  ,to_char(add_months(date'1972-05-31',1), 'yyyy-mm-dd') "ostatni dzień miesiąca"
  ,months_between(date'2022-05-20', date'2022-04-20') "dokładnie miesiąc"
  ,months_between(date'2022-05-20', date'2022-04-19') "miesiąc+1 dzień"
FROM
  dual;

Czyli pomiędzy datami 20 maja 2022, a 20 kwietnia 2022 jest dokładnie 1 miesiąc różnicy. Oracle uwzględni różną liczbę dni w miesiącach.

Natomiast pomiędzy 20 maja 2022, a 19 kwietnia 2022 jest 1 miesiąc i 1 dzień. Ten jeden dzień zostanie pokazany jako 1/31 miesiąca.

Więcej na temat arytmetyki datoczasowej w nadchodzącej lekcji o odcinkach czasu.

Wydajność

Przed wysłaniem danych do klienta Oracle formatuje dane datoczasowe w napis. Warto zadbać aby format był taki jak potrzebujesz aby nie było potrzeby przeformatowania w aplikacji.

Gdy używasz funkcji przekształcających w klauzuli WHERE zadbaj aby parametrem był literał, a nie kolumna. Użycie przekształcenia na kolumnie zaowocuje wykonaniem go na wszystkich rekordach tabeli oraz uniemożliwi użycie indeksu. Przekształcenie literału zostanie wykonane jedynie raz.

Zniechęcam do polegania na konwersji niejawnej.

Podsumowanie

Gratuluję Ci ukończenia osiemnastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak formatować i przekształcać dane datoczasowe aby były zrozumiałe dla Oracle i przyjazne dla człowieka. Wiesz także na co zwrócić uwagę aby wyszukiwanie danych datoczasowych było efektywne.

Zadanie dla Ciebie: napisz i uruchom zapytanie zwracające nazwę dnia tygodnia Twoich przyszłorocznych imienin w języku hiszpańskim. Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak używać słów kluczowych date i timestamp do przekształcania literału zawierającego datę w wartość typu datoczasowego. Wiesz także jak wykorzystać funkcje przekształcające do pracy z danymi datoczasowymi. W następnej lekcji zaprezentuję obsługę odcinków czasu.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji