Zaokrąglanie i przycinanie danych czasowych: DATE_TRUNC()

DATE_TRUNC() — to potężne narzędzie, które pozwala ci przyciąć wartości czasowe do określonej jednostki czasu. Na przykład, dzięki niemu możesz zaokrąglić znacznik czasu do początku dnia, miesiąca, roku, godziny i tak dalej. To szczególnie przydatne przy analizie danych w podziale na okresy (np. jeśli chcesz pogrupować zamówienia według dnia, miesiąca albo roku).

Wyobraź sobie datę i godzinę jako długi ciąg tekstu, gdzie masz godziny, minuty, sekundy. Funkcja DATE_TRUNC() bierze ten ciąg i "przycina" zbędne części, zostawiając tylko to, co ci potrzebne. Na przykład:

• Chcesz przyciąć datę 2023-10-01 15:30:45 do początku dnia. Wynik to 2023-10-01 00:00:00.
• Albo chcesz zostawić tylko pierwszą sekundę godziny, czyli 2023-10-01 15:00:00.

Składnia

Składnia funkcji DATE_TRUNC() wygląda tak:

DATE_TRUNC(field, source)

• field — to jednostka czasu, do której chcesz "przyciąć" datę. Na przykład, year, month, day, hour, minute.
• source — to wartość czasowa, którą chcesz przyciąć. Może to być kolumna typu TIMESTAMP albo wynik innej funkcji, np. NOW().

Przykład prostego wywołania:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Wynik: 2023-10-01 00:00:00

Obsługiwane pola

Oto lista niektórych obsługiwanych jednostek czasu, których możesz użyć w DATE_TRUNC():

Im mniejsza jednostka czasu, tym dokładniejszy będzie wynik przycięcia. A tak przy okazji, tydzień zaczyna się od niedzieli :)

Przykłady użycia DATE_TRUNC()

Przycinanie do początku dnia. W tym przykładzie bierzemy znacznik czasu i zaokrąglamy go do początku dnia:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Wynik: 2023-10-01 00:00:00

Przycinanie do początku miesiąca. Teraz przytniemy datę do początku miesiąca:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Wynik: 2023-10-01 00:00:00

Przycinanie do początku roku. Spróbujmy zaokrąglić datę do początku roku:

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Wynik: 2023-01-01 00:00:00

Użycie z aktualnym czasem (NOW()). Jeśli chcesz zawsze pracować z bieżącą datą i godziną, możesz połączyć DATE_TRUNC() i NOW():

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- Wynik zależy od aktualnego czasu, np.: 2023-10-01 15:00:00

Grupowanie zamówień po miesiącach. Teraz przejdźmy do bardziej praktycznego przykładu. Załóżmy, że mamy tabelę z zamówieniami, gdzie dla każdego wpisu jest data zamówienia. Chcemy policzyć liczbę zamówień w każdym miesiącu:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Wynik:

Praktyczne przypadki użycia

Analiza danych czasowych według okresów: chcesz wiedzieć, ilu użytkowników zarejestrowało się każdego roku, miesiąca albo dnia? Użyj DATE_TRUNC() do grupowania danych.

Tworzenie raportów: odpowiednie zaokrąglenie znacznika czasu sprawi, że raporty będą czytelniejsze.

Porównywanie daty i czasu: jeśli masz dane czasowe z bardzo dokładnym znacznikiem (np. z milisekundami), przytnij je do odpowiedniego poziomu, żeby porównania były poprawne.

Typowe błędy przy użyciu DATE_TRUNC()

Użycie nieobsługiwanych pól. Na przykład pole millisecond nie jest obsługiwane i próba jego użycia spowoduje błąd.

Nieprawidłowy typ danych. Funkcja DATE_TRUNC() działa tylko z typami czasowymi, takimi jak TIMESTAMP. Jeśli przekażesz jej tekst, dostaniesz błąd.

Błąd zaokrąglenia. Pamiętaj, że DATE_TRUNC() zawsze przycina czas do początku wskazanej jednostki czasu. Jeśli chcesz zaokrąglić czas, warto użyć innych metod.