Temporal Validity
Dariusz Grzywacz
W ostatnim moim projekcie nad którym pracowałem część danych powinna zawierać czasowy znacznik ważności. Mówiąc krótko mamy dane, które posiadają jakąś datę ważności początkową i końcową. W związku z tym chciałem bliżej przyjrzeć się wprowadzonemu w Oracle Database 12c Release 1 (12.1) Temporal Validity.
Zgodnie z dokumentacją Oracle pkt. 1.9.4 Temporal Validity Support pozwala powiązać jeden lub więcej ważnych wymiarów czasowych z tabelą i sprawić, że dane będą widoczne w zależności od ich ważności czasowej, określonej przez datę początkową i końcową lub znaczniki okresu czasu, dla którego dany rekord jest uważany za ważny. Pozwala to na uproszczenie zapytań o efektywne zakresy dat.
Tworzymy tabelę
W celu przetestowania funkcjonalności utworzymy przykładową tabelę emp_example. Na podstawie wstawianych do niej rekordów dokonamy analizy okresu ważności poszczególnych rekordów. Tabela będzie zawierała podstawowe dane o pracowniku oraz znaczniki czasowe ważności rekordu.
create table emp_example (
id number generated always as identity increment by 1 start with 1 minvalue 1 nomaxvalue
, empno number
, first_name varchar2(64)
, last_name varchar2(64)
, start_date timestamp
, end_date timestamp
, constraint my_emp_pk primary key ( id )
, period for emp_valid_time ( start_date, end_date )
);
Table EMP_EXAMPLE created.
Po utworzeniu tabeli, automatycznie powstał constraint EMP_VALID_TIMED04C5F z warunkami: (START_DATE < END_DATE) and (EMP_VALID_TIME > 0)
Wstawiamy jeden rekord, który w zupełności wystarczy nam do testów.
insert into emp_example values (default, '101', 'Jaś', 'Fasola', date'2025-04-01', date'2025-04-30');
select * from emp_example;
Test funkcjonalności
Pomyślmy o datach w odniesieniu do osi czasu. Jest to najbardziej efektywne w przypadku pytań o nakładanie się zakresów dat i można je łatwo zwizualizować za pomocą diagramu takiego jak ten poniżej. Mamy oś czasu oraz dane w tabeli, które określają jakąś data początkową i data końcową. Może to być np. data trwania umowy, data ważności uprawnień, wszystko to zależy od założeń biznesowych.
Załóżmy, że Jaś Fasola ma uprawnienia do jakieś systemu co w naszej tabeli reprezentuje rekord z datą początkową 1 kwietnia 2025 i datą końcową 30 kwietnia 2025 roku. Chcemy teraz sprawdzić okres ważności w określonym punkcie czasu.
Poniżej przedstawiam pełen zestaw próbkowanych dat:
Aby wyświetlić dane pracownika, który jest aktywny w określonym punkcie czasu, używamy następującego zapytania: select * from emp_example as of period for emp_valid_time sysdate;
Sprawdźmy jakie uzyskamy rezultaty w poszczególnych punktach czasowych.
--------------------------------------------------------------------------------------------------------
-- Przypadek 1, punkt czasowy przekroczył zakres dat z tabeli
-- +------------+
-- +
select * from emp_example as of period for emp_valid_time date'2025-05-01';
-- no rows selected
--------------------------------------------------------------------------------------------------------
-- Przypadek 2, punkt czasowy jest równy end_date
-- +------------+
-- +
select * from emp_example as of period for emp_valid_time date'2025-04-30';
-- no rows selected
-- wygląda na to że zbiór wartości spełniający warunek jest z prawej strony niedomknięty
-- graniczna data 2025-04-30, nie spełnia warunku
--------------------------------------------------------------------------------------------------------
-- Przypadek 3, punkt czasowy zawiera się w przedziale dat
-- +------------+
-- +
select * from emp_example as of period for emp_valid_time date'2025-04-20';
ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--- ---------- ----------- ---------- ----------------------------- -----------------------------
1 101 Jaś Fasola 2025-04-01 00:00:00,000000000 2025-04-30 00:00:00,000000000
--------------------------------------------------------------------------------------------------------
-- Przypadek 4, punkt czasowy jest równy dacie początkowej
-- +------------+
-- +
select * from emp_example as of period for emp_valid_time date'2025-04-01';
ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--- ---------- ----------- ---------- ----------------------------- -----------------------------
1 101 Jaś Fasola 2025-04-01 00:00:00,000000000 2025-04-30 00:00:00,000000000
--------------------------------------------------------------------------------------------------------
-- Przypadek 5, punkt czasowy jest przed datą początkową
-- +------------+
-- +
select * from emp_example as of period for emp_valid_time date'2025-03-31';
-- no rows selected
Po analizie dochodzimy do wniosku, że odpowiednikiem dla naszego zapytania
select * from emp_example as of period for emp_valid_time sysdate;
jest
select * from emp_example where sysdate >= start_date and sysdate < end_date;
Zapytanie zwraca rekordy jeżeli punkt czasowy zawiera się w przedziale [2025-04-01 , 2025-04-30), inaczej mówiąc lewostronnie domkniętym i prawostronnie otwartym. Wniosek z tego jest taki, że pracownik posiada uprawnienia od północy 1 kwietnia i traci je dokładnie o północy 30 kwietnia. Jeżeli pracownik miałby mieć uprawnienia do końca dnia 30 kwietnia, to end_date powinno wskazywać na północ w dniu 1 maja 2025.
Prześledziliśmy jeden punkt czasu, ale zweryfikujemy jeszcze inną wersje zapytania. Sprawdźmy wersję z nakładaniem się zakresów czasowych. W tym przypadku używamy zapytania:
select * from emp_example versions period for emp_valid_time between DATA1 and DATA2.
W przypadku zakresu danych w Oracle mamy nie udokumentowaną funkcję overlaps, którą również przy tej okazji przetestujemy. Potraktujmy to bardziej jako ciekawostkę i nie używajmy tej funkcji w projektach na środowisku produkcyjnym.
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- PRZEDZIAŁY
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
-- 1 Rozdzielne zakresy
-- +------------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-05-01' and date'2025-05-31';
-- zwrot:
-- no rows selected
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-05-01'), to_timestamp('2025-05-31'));
-- zwrot:
-- no rows selected
--------------------------------------------------------------------------------------------------------
-- 2 Zakresy mają dokładnie jeden punkt wspólny (end_date)
-- +------------+
-- +-------------+
select * from emp_example
versions period for emp_valid_time between date'2025-04-30' and date'2025-05-31';
-- zwrot:
-- no rows selected
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-04-30'), to_timestamp('2025-05-31'));
-- zwrot:
-- no rows selected
--------------------------------------------------------------------------------------------------------
-- 3 wspólny zakres jak poniżej
+------------+
+------------+
select * from emp_example
versions period for emp_valid_time between date'2025-04-15' and date'2025-05-31';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-04-15'), to_timestamp('2025-05-31'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
--------------------------------------------------------------------------------------------------------
-- 4 wspólny zakres jak poniżej
+------------+
+------+
select * from emp_example
versions period for emp_valid_time between date'2025-04-15' and date'2025-04-30';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-04-15'), to_timestamp('2025-04-30'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
---------------------------------------------------------------------------------------------------------
-- 5 drugi zawiera się w jeden jak poniżej
-- +------------+
-- +------+
select * from emp_example
versions period for emp_valid_time between date'2025-04-15' and date'2025-04-20';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-04-15'), to_timestamp('2025-04-20'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-05-01 00:00:00,0
----------------------------------------------------------------------------------------------------------
-- 6 jeden i dwa są sobie równe
-- +------------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-04-01' and date'2025-04-30';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-04-01'), to_timestamp('2025-04-30'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
----------------------------------------------------------------------------------------------------------
-- 7 jeden zawiera się w dwa jak poniżej
-- +------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-03-01' and date'2025-06-30';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-05-01 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-03-01'), to_timestamp('2025-06-30'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
----------------------------------------------------------------------------------------------------------
-- 8 jeden zawiera się w dwa jak poniżej
-- +------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-03-01' and date'2025-04-30';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-03-01'), to_timestamp('2025-04-30'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
----------------------------------------------------------------------------------------------------------
-- 9 jeden i dwa mają wspólny zakres jak poniżej
-- +------------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-03-01' and date'2025-04-15';
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-03-01'), to_timestamp('2025-04-15'));
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
----------------------------------------------------------------------------------------------------------
-- 10 jeden i dwa stykają w pukcie start_date
-- +------------+
-- +------------+
select * from emp_example
versions period for emp_valid_time between date'2025-03-01' and date'2025-04-01'
-- zwrot:
--ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
--1 101 Jaś Fasola 2025-04-01 00:00:00,0 2025-04-30 00:00:00,0
;
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-03-01'), to_timestamp('2025-04-01'));
-- zwrot:
-- no rows selected
-- różnica pomiędzy "versions period for emp_valid_time" a "overlaps"
-- s2 >
----------------------------------------------------------------------------------------------------------
-- 11 jeden i dwa nie mają punktów wspólnych jak poniżej
+------------+
+------------+
select * from emp_example
versions period for emp_valid_time between date'2025-03-01' and date'2025-03-31';
-- zwrot:
-- no rows selected
select * from emp_example
where (start_date, end_date) overlaps (to_timestamp('2025-03-01'), to_timestamp('2025-03-31'));
-- zwrot:
-- no rows selected
---------------------------------------------------------------------------------------------------------
Nie będę omawiał szczegółowo poszczególnych przypadków, ale spójrzmy tylko na wybrane, które bez wcześniejszej analizy nie muszą być tak oczywiste.
W punkcie 2 otrzymaliśmy wynik “no rows selected” i w zasadzie należało się tego spodziewać mając na uwadze wcześniejsze rozważania z jednym punktem czasowym. Po prostu end_date jest datą która “nie wchodzi” do pierwszego zakresu czasowego.
Natomiast w punkcie 10 w przypadku Temporal Validity wynik jest również oczywisty, ponieważ start_date zawiera się w zakresie czasowym nr 1, natomiast wynik funkcji overlap jest zupełnie inny, gdyż nie zwraca żadnego rekordu. Z powyższego widać, że pomiędzy tymi funkcjami są jednak różnice.
Po dokładnym przeanalizowaniu wyników możemy napisać odpowiedniki kod w SQL zastępujący testowane funkcje.
---------------------------------------------------------------------
-- Temporal Validity
select * from emp_example
versions period for emp_valid_time between START_2 and END_2;
-- odpowiednikiem jest
select * from emp_example
where start_date <= END_2 and end_date > START_2;
----------------------------------------------------------------------
-- overlaps
select * from emp_example
where (start_date, end_date) overlaps (START_2, END_2);
-- odpowiednikiem jest
select * from emp_example
where start_date < END_2 and end_date > START_2;
Załóżmy, że nasz pracownik powinien mieć uprawnienia przez cały kwiecień. Poprawmy uprawnienia zgodnie działaniem Temporal Validity i ustawmy w tabeli datę końca uprawnień czyli północ w dniu 1 maja 2025. Odnówmy również uprawnienia pracownika od pierwszego maja na czas nieokreślony (END_DATE = NULL) . Po update i insert tabeli powinniśmy uzyskać zapisy jak poniżej.
Gdy teraz sprawdzimy okres ważności dla daty 2 maja 2025 roku, to zauważymy prostotę zapytania Temporal Validity, w którym NULL jest traktowany jako brak ograniczenia czasowego.
-- sprawdzamy kto ma uprawnienia w dniu 2 maja
select * from emp_example as of period for emp_valid_time date'2025-05-02';
ID EMPNO FIRST_NAME LAST_NAME START_DATE END_DATE
-- ----- -------------- ---------- ----------------------------- ---------
3 101 Jaś Fasola 2025-05-01 00:00:00,000000000
Moim zdaniem warto skorzystać z tego rozwiązania choćby ze względu na prostotę budowania zapytań SQL. Mam nadzieję, że przybliżyłem nieco obsługę Temporal Validity i w jaki sposób należy zaprojektować schemat tabeli aby obliczenia przebiegały prawidłowo.
Subscribe to my newsletter
Read articles from Dariusz Grzywacz directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by