Praca z przedziałami czasowymi: INTERVAL

Trening trwa półtorej godziny, dzień pracy — 8 godzin, przerwa — 20 minut, a gotowanie jajek zajmuje 10 minut. To wszystko — o przedziałach czasowych.

Przedziały czasowe są przydatne w prawdziwych aplikacjach, kiedy trzeba na przykład policzyć terminy wykonania zadań, określić różnicę między dwiema datami albo dodać/odjąć czas. Ale zanim wejdziemy w szczegóły, szybko przypomnijmy sobie, gdzie skończyliśmy na poprzednich wykładach.

W PostgreSQL INTERVAL — to specjalny typ danych przeznaczony do reprezentowania okresów czasu. W przeciwieństwie do innych typów czasowych, nie przechowuje konkretnej daty ani godziny, tylko opisuje okres, na przykład: "2 dni", "3 godziny", "5 minut".

Składnia INTERVAL

Przedziały można podawać w formacie:

INTERVAL 'liczba jednostka_czasu'

Przykłady:

INTERVAL '2 days' -- Dwa dni
INTERVAL '3 hours' -- Trzy godziny
INTERVAL '15 minutes' -- Piętnaście minut
INTERVAL '1 day 2 hours' -- Jeden dzień i dwie godziny

PostgreSQL obsługuje różne jednostki czasu: year, month, day, hour, minute, second. Możesz je łączyć w jednym wyrażeniu.

Użycie przedziałów w zapytaniach

Typ danych INTERVAL robi się szczególnie przydatny, gdy zaczynasz go używać razem z innymi typami czasowymi (DATE, TIMESTAMP). Zobaczmy podstawowe operacje.

Dodawanie przedziału do daty/czasu

Przedziały można dodawać do wartości daty lub czasu. Na przykład:

SELECT CURRENT_DATE + INTERVAL '7 days' AS delivery_date;
-- Dostajemy datę za 7 dni od teraz

Wynik:

Odejmowanie przedziałów od daty/czasu

Oprócz dodawania, można też odejmować przedziały:

SELECT NOW() - INTERVAL '2 hours' AS two_hours_ago;
-- Dostajemy czas dwie godziny temu

Wynik:

Obliczanie różnicy między dwiema datami

Jedną z mocnych funkcji INTERVAL jest liczenie różnicy między dwiema datami:

SELECT '2023-10-15'::DATE - '2023-10-01'::DATE AS days_difference;
-- Ile dni między dwiema datami

Wynik:

Zwróć uwagę, że wynik w tym przypadku też jest przedstawiony jako przedział czasowy.

Praktyczne przykłady pracy z INTERVAL

Obliczanie daty dostawy. Wyobraź sobie sklep internetowy, gdzie dostawa trwa od 3 do 7 dni. Tak można policzyć możliwe daty dostawy:

SELECT CURRENT_DATE + INTERVAL '3 days' AS earliest_delivery,
       CURRENT_DATE + INTERVAL '7 days' AS latest_delivery;

Wynik:

Ustalanie czasu wykonania zadania. Załóżmy, że jest tabela tasks, gdzie każda taska ma podaną datę startu. Trzeba policzyć datę zakończenia zadania biorąc pod uwagę jego długość w godzinach:

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name TEXT,
    start_time TIMESTAMP,
    duration INTERVAL
);

INSERT INTO tasks (task_name, start_time, duration)
VALUES
('Przygotuj raport', '2023-10-01 09:00:00', INTERVAL '4 hours'),
('Testuj aplikację', '2023-10-01 10:00:00', INTERVAL '2 hours 30 minutes');

SELECT task_name,
       start_time,
       start_time + duration AS end_time
FROM tasks;

Wynik:

Porównywanie czasu wykonania. Czasem trzeba znaleźć wszystkie zadania, które zostały wykonane w mniej niż 3 godziny:

SELECT task_name
FROM tasks
WHERE duration < INTERVAL '3 hours';

Wynik:

Przydatne triki z przedziałami

Dynamiczne obliczanie przedziałów. Możesz łączyć przedziały z innymi operacjami. Na przykład, wziąć liczbę dni z kolumny i zamienić ją na przedział:

SELECT CURRENT_DATE + (order_days || ' days')::INTERVAL AS order_due_date
FROM orders;

Konwersja przedziału na tekst. Czasem trzeba zamienić przedział na tekst do wyświetlenia:

SELECT INTERVAL '2 days 3 hours'::TEXT AS interval_text;

Wynik:

Typowe błędy przy pracy z INTERVAL

Praca z INTERVAL, mimo że jest mocny, może sprawiać trochę problemów. Jeden z typowych błędów — użycie złego formatu. Na przykład, jeśli spróbujesz INTERVAL '2 hours and 30 minutes', dostaniesz błąd, bo PostgreSQL nie rozumie słowa "and". Poprawnie: INTERVAL '2 hours 30 minutes'.

Kolejny błąd — zapomnieć podać jednostkę czasu, jak w INTERVAL '2'. PostgreSQL nie wie, co to za "2", więc zawsze podawaj jednostki (2 days, 2 hours).