Jak obsługiwać odcinki czasu w zapytaniach SQL? – lekcja 19

Wstęp

Odcinek czasu w bazie danych może być składowany w postaci dwóch kolumn przechowujących datę początkową i końcową odcinka. Czas trwania reprezentowany jest danymi typu INTERVAL. Przy pomocy predykatu OVERLAPS wyszukasz zachodzące odcinki czasu. Tego i jeszcze więcej o obsłudze przedziałów czasu w Oracle dowiesz się z niniejszej lekcji.

Typy interwałowe

Dwa rodzaje typu interwałowego różnią się zakresami wartości.

• INTERVAL YEAR TO MONTH pozwala na pracę z interwałami o długości od lat do miesiąca.
• INTERVAL DAY TO SECOND pozwala na pracę z interwałami od dni do ułamkowej części sekundy.

Liczby w nawiasach określają ilość cyfr. Gdy pominięte przyjmują wartości domyślne. W moim przypadku ilość lat można określić na trzech cyfrach, miesięcy na czterech, dni na pięciu. Natomiast sześć przy sekundach oznacza sześć cyfr określających ułamkową część sekundy.

• pierwsze zastosowanie interwałów jest oczywiste: przechowywanie w bazie danych informacji o tym ile trwała jakaś czynność.
• drugie zastosowanie to poprawa czytelności arytmetyki datoczasowej. Zamiast używania ułamkowych części dnia lub specjalnych funkcji można użyć interwału.

Jakie inne zastosowania Ty widzisz?

Konwersja literałów

Gdy konwertujesz literały oznaczające przedział czasu do wartości typu INTERVAL musisz pamiętać aby lata od miesięcy oddzielić myślnikiem. Dni od godzin spacją. Godziny od minut i minuty od sekund dwukropkiem. A pomiędzy sekundami i ułamkowymi częściami sekundy jest kropka. Jest to standardowa notacja ANSI. SQL Server ma podobnie. Za to PostgreSQL ma bardziej elastyczne podejście.

SELECT 
  INTERVAL '120-3' YEAR(3) TO MONTH "120 lat i 3 miesice"
  ,INTERVAL '100 10' DAY(3) TO HOUR "100 dni i 10 godzin"
  ,INTERVAL '09:08:07.123' HOUR TO SECOND(3) "9 godzin, 8 minut, 7 sekund i 123 milisekundy"
  ,INTERVAL '2' HOUR "2 godziny"
FROM
  dual;

Zwróć uwagę, że końcowe pole musi być o mniejszym znaczeniu niż początkowe. Czyli jeśli pierwsze to dni to drugie może być co najwyżej godziny. Niemniej nie musisz wskazywać końcowego pola.

Funkcje konwertujące

Zamiast konwertowania literału słowem kluczowym INTERVAL możesz posłużyć się funkcjami.

• TO_YMINTERVAL konwertuje napis do typu YEAR TO MONTH.
• TO_DSINTERVAL konwertuje napis do typu DAY TO SECOND.

Obowiązują te same ścisłe reguły formatowania napisu co w przypadku klauzuli INTERVAL.

SELECT
  TO_YMINTERVAL('5-11') "5 lat, 11 miesięcy"
  ,TO_DSINTERVAL('3 12:13:14.567') "3 dni, 12 godzin, 13 minut, 14 sekund, 567 milisekund"
FROM
  dual;

Dwie kolejne funkcje pozwalają na konwersję liczby całkowitej do danych typu INTERVAL.

• NUMTOYMINTERVAL konwertuje liczbę całkowitą z pierwszego parametru do typu YEAR TO MONTH. Drugim parametrem wskazujesz czy dotyczy lat czy miesięcy.
• NUMTODSINTERVAL konwertuje liczbę całkowitą z pierwszego parametru do typu DAY TO SECOND. Drugim parametrem opisujesz czy dotyczy dni, godzin czy sekund.

Czyli pierwszy parametr to liczba jednostek wskazanych drugim parametrem.

SELECT
  NUMTOYMINTERVAL(2, 'YEAR') "2 lata"
  ,NUMTODSINTERVAL(5, 'MINUTE') "5 minut"
FROM
  dual;

Arytmetyka

Możesz dodawać i odejmować interwały z typami date i timestamp.

SELECT
  sysdate+INTERVAL '5' MONTH "zwraca typ date"
  ,systimestamp-INTERVAL '22' DAY  "zwraca typ timestamp"
FROM
  dual;

Taki zapis jest bardziej czytelny niż znane z lekcji o punktach w czasie dodawanie czy odejmowanie liczb oznaczających części doby. Pamiętaj także, że operacja arytmetyczna wykonana na typie datoczasowym może dać w wyniku inny typ datoczasowy. Dodając lub odejmując liczby do danych typu timestamp wynik dostaniesz typu date. Czyli utracisz informacje o ułamkowych częściach sekundy oraz o strefie czasowej.

Z kolei odejmując od siebie typy datoczasowe liczbę w wyniku dostaniesz jedynie w przypadku gdy oba operandy będą typu date. W innym przypadku dostaniesz interwał.

Interwały możesz dodawać i odejmować od siebie tylko gdy są tego samego rodzaju. Czyli nie możesz dodać ani odjąć od siebie INTERVAL YEAR TO MONTH i INTERVAL DAY TO SECOND.

W relacyjnych bazach danych bardzo ważne jest przestrzeganie typów. Dzięki temu Twoje zapytania będą działać optymalnie i dawać oczekiwane wyniki. Pamiętaj, że odejmowanie dwóch wartości typu date da w efekcie liczbę określającą liczbę dni dzielącą obie daty. Natomiast odejmowanie dwóch wartości typu timestamp da w efekcie wartość typu INTERVAL DAY TO SECOND.

SELECT
  sysdate - date'2024-05-20' "liczba dni"
  ,systimestamp - timestamp'2024-05-20 12:13:14' "interwał"
FROM
  dual;

Możesz to zmienić umieszczając operację arytmetyczną w nawiasach i deklarując typ interwałowy.

SELECT
  (sysdate - date'2024-05-20') DAY TO SECOND "interwał"
  ,(systimestamp - timestamp'2024-05-20 12:13:14') YEAR TO MONTH "interwał"
FROM
  dual;

Zwróć uwagę aby liczba dni konwertowana na DAY TO SECOND mieściła się w typie.

Wyłuskanie

Jeśli potrzebujesz z daty lub interwału wyłuskać jedynie jedną składową jak rok, miesiąc czy minuta to posłuż się należącą do standardu ANSI funkcją EXTRACT. Pierwszym parametrem opisujesz interesującą Cię część składową danych datoczasowych. Następnie umieszczasz słowo kluczowe FROM i wyrażenie z którego chcesz wyłuskać składową.

SELECT
  extract(YEAR FROM sysdate) "bieżący rok"
  ,extract(TIMEZONE_HOUR FROM systimestamp) "strefa czasowa godzinowo"
  ,extract(SECOND FROM to_dsinterval('1 2:3:5')) "liczba sekund"
FROM
  dual;

Zwróć uwagę, że parametr opisujący składową daty nie jest umieszczony w apostrofach. Pełną listę składowych daty znajdziesz w dokumentacji.

Kolizyjne odcinki czasu – OVERLAPS

Odcinek czasu może mieć część wspólną z innym odcinkiem czasu. Czyli jeden przedział czasu zachodzi na inny. Do testowania czy dwa odcinki czasu współdzielą jakiś fragment możesz wykorzystać nieudokumentowaną funkcję OVERLAPS. Informacje na ten temat znajdziesz w My Oracle Support: Undocumented OVERLAPS function. How does it work? What is the Workaround? (Doc ID 1056382.1)

Funkcja OVERLAPS zwraca prawdę gdy dwa odcinki czasu mają więcej części wspólnej niż tylko jeden punkt. Czyli gdy jeden odcinek czasu kończy się w punkcie w którym drugi się zaczyna to według OVERLAPS te odcinki się nie pokrywają. Odcinki czasu możesz podać w formie punktu początkowego i końcowego.

SELECT 'kolizja' FROM dual WHERE
(date'2022-05-20', date'2023-05-20')
OVERLAPS
(date'2022-06-01', date'2023-01-01');

SELECT 'kolizja' FROM dual WHERE
(date'2022-05-20', date'2023-05-20')
OVERLAPS
(date'2022-06-01', date'2023-07-01');

SELECT 'kolizja' FROM dual WHERE
(date'2022-07-20', date'2023-05-20')
OVERLAPS
(date'2022-06-01', date'2023-01-01');

SELECT 'brak kolizji' FROM dual WHERE NOT
(date'2022-07-20', date'2023-05-20')
OVERLAPS
(date'2023-05-20', date'2023-09-01');

Lub w formie punktu początkowego i interwału.

SELECT 'kolizja' FROM dual WHERE
(date'2022-05-20', date'2023-05-20')
OVERLAPS
(date'2022-06-01', interval '1' month);

SELECT 'kolizja' FROM dual WHERE
(date'2022-05-20', interval '1' year)
OVERLAPS
(date'2022-06-01', interval '2' year);

Uwaga: implementacja OVERLAPS nie jest ukończona i funkcja ta nie jest wspierana przez Oracle.

Kolizyjne odcinki czasu – tradycyjnie

Tę samą funkcjonalność uzyskasz przy pomocy odpowiednich warunków w klauzuli WHERE.

SELECT 'kolizja' FROM dual WHERE
(date'2022-05-20' <= date'2023-01-01')
and
(date'2023-05-20' >= date'2022-06-01');

SELECT 'kolizja' FROM dual WHERE
date'2023-05-20' BETWEEN date'2022-06-01' AND date'2023-01-01'
or
date'2023-01-01' BETWEEN date'2022-05-20' AND date'2023-05-20';

SELECT 'kolizja' FROM dual WHERE
greatest(date'2022-05-20', date'2022-06-01') < least(date'2023-05-20',date'2023-01-01');

Pierwsze i drugie zapytanie jest w stanie użyć indeksów na kolumnach. Trzecie nie użyje indeksów ze względu na wykorzystanie funkcji greatest i least. Może więc być nieoptymalne jeśli chodzi o wydajność. Wszystkie trzy są przerobionym na tradycyjny SQL zapytaniem pierwszym wykorzystującym OVERLAPS. Samodzielnie napisz zapytania wykorzystujące tradycyjny SQL dla pozostałych i pochwal się nimi na discord.

• greatest zwraca największą wartość ze wszystkich przekazanych jako parametry.
• least zwraca najmniejszą wartość ze wszystkich przekazanych.

Parametrów musi być nie mniej niż jeden. Mogą być dowolnego typu. Zwrócona wartość będzie tego samego typu jakiego jest pierwszy parametr.

Operator BETWEEN zwraca prawdę jeśli pierwszy z argumentów mieści się w przedziale opisanym dwoma kolejnymi argumentami. Włączając w przedział wartości obu argumentów. Działa dla różnych typów danych.

Czas obowiązywania

Oracle udostępnia klauzulę PERIOD FOR, która może być dodana do istniejących tabel bez potrzeby ich kosztownej modyfikacji. Czyli jeśli przechowujesz odcinki czasu jako kolumnę z datą początkową i kolumnę z datą końcową to możesz je spiąć klauzulą PERIOD FOR.

ALTER TABLE t
  ADD PERIOD FOR okres(pocz, kon);

Dzięki temu w zapytaniach w prosty sposób możesz testować czy dane w tabeli obowiązują dla wskazanej daty lub okresu. Z angielskiego temporal validity.

SELECT -- dla wskazanej daty
  * 
FROM
  t AS OF PERIOD FOR okres sysdate;

SELECT -- dla zadanego okresu
  * 
FROM
  t VERSIONS PERIOD FOR okres BETWEEN sysdate AND sysdate+interval '7' day;

Na przykład znaleźć studentów uczęszczających w zadanym czasie, rezerwacje z zadanego czasu czy obowiązujące oferty. Jakie jeszcze zastosowania przychodzą Ci do głowy?

Temat jedynie sygnalizuję. Będzie rozbudowany w kursie zaawansowanym, kursie o projektowaniu bazy danych lub dedykowanym materiale do realizacji którego możesz namówić mnie w komentarzach.

Podsumowanie

Gratuluję Ci ukończenia dziewiętnastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak posługiwać się typem INTERVAL oraz wykrywać kolizje odcinków czasu funkcją OVERLAPS i tradycyjnym SQL.

Zadanie dla Ciebie: Zaprezentowane przeze mnie zapytania używające funkcji OVERLAPS przepisz na tradycyjny SQL wszystkimi trzema metodami i uruchom.

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ć danych typu INTERVAL oraz różnymi metodami wykrywać zachodzące na siebie odcinki czasu. W następnej lekcji zaprezentuję wybieranie liczb.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji