Konwersja formatów dat

Świat baz danych i frontend często nie dogaduje się co do tego, jak dokładnie mają wyglądać daty. PostgreSQL może trzymać daty jako DATE, TIMESTAMP albo nawet TIMESTAMPTZ, ale ten format nie zawsze pasuje do pokazania użytkownikowi. Na przykład, zamiast standardowego 2023-10-01 12:30:45, designerzy mogą chcieć zobaczyć 01 października 2023 roku, 12:30. A czasem trzeba sformatować datę do raportu albo API.

Do konwersji dat na string i odwrotnie w PostgreSQL są funkcje TO_CHAR() i TO_DATE().

Funkcja TO_CHAR()

TO_CHAR() — to twój najlepszy ziomek, gdy trzeba z danych czasowych zrobić czytelny string. Bierze datę albo znacznik czasu i formatuje ją według podanego wzorca.

Składnia

TO_CHAR(value, format)

• value — data albo znacznik czasu, który chcesz przekonwertować.
• format — string ze wzorcem formatu, jak dokładnie chcesz pokazać datę.

Przykłady formatów

Pełną listę formatów znajdziesz w oficjalnej dokumentacji PostgreSQL.

Przykłady użycia TO_CHAR()

Formatowanie daty do raportu

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Wynik: '09.10.2023'

Wyświetlanie czasu w formacie 12-godzinnym

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Wynik: '03:45 PM'

Wyświetlanie miesiąca słownie

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Wynik: 'October '

Uwaga: PostgreSQL dodaje spację na końcu. To feature, nie bug! Żeby usunąć spacje, użyj funkcji TRIM():

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Tworzenie customowego formatu

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Wynik: '2023/10/09 15:45:30'

Formatowanie dla interfejsu użytkownika

SELECT TO_CHAR(NOW(), 'DD "października" YYYY roku') AS user_friendly_date;
-- Wynik: '09 października 2023 roku'

Funkcja TO_DATE()

TO_DATE() robi odwrotnie: bierze string i zamienia go na typ DATE. Po co to? Na przykład, użytkownik może wpisać datę w formacie 01-10-2023, i PostgreSQL musi "zrozumieć", co to za data.

Składnia

TO_DATE(value, format)

• value — string zawierający datę.
• format — string ze wzorcem opisującym format stringa.<

Przykłady użycia TO_DATE()

Konwersja stringa na datę

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Wynik: '2023-10-01' (typ: DATE)

Porównanie stringowej daty z datą w tabeli

Załóżmy, że mamy tabelę appointments z kolumną appointment_date typu DATE. Użytkownik wpisuje datę jako string:

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Zły format

Ważne: jeśli format stringa nie zgadza się ze wzorcem, będzie błąd! Na przykład:

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Błąd: nieprawidłowy format wejściowy

Sprawdzanie danych od użytkownika

Załóżmy, że tworzymy tabelę do trzymania zamówień, gdzie data jest wpisywana przez użytkownika:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Wstawianie danych z konwersją stringa na datę
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Praktyczne przykłady

Formatowanie raportu. W tabeli sales trzymana jest data sprzedaży w kolumnie sale_date (typ TIMESTAMP). Trzeba zrobić raport, gdzie daty będą w formacie DD.MM.YYYY.

-- Przykładowe dane
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Raport
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Konwersja danych od użytkownika. Załóżmy, że użytkownik wpisuje datę w formacie string MM/DD/YYYY. Trzeba przekonwertować ją na DATE, żeby zapisać w systemie.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Typowe błędy i tipy

Zły format. Często pojawia się błąd, gdy format stringa nie zgadza się ze wzorcem. Na przykład, jeśli użytkownik wpisał datę jako 01-10-2023, a format to MM/DD/YYYY, PostgreSQL rzuci błąd. Tip: zawsze waliduj dane od użytkownika zanim wrzucisz je do SQL.

Spacje w formatach TO_CHAR(). Niektóre formaty, jak MONTH, dodają spacje. Jeśli to problem, użyj funkcji TRIM().

Błędy przy parsowaniu stringów. Jeśli string ma nieoczekiwane znaki albo format, PostgreSQL nie da rady go przekonwertować. Tip: używaj wyrażeń regularnych albo dodatkowych sprawdzeń danych przed wrzuceniem do bazy.

Nieprawidłowe użycie formatów czasu. Na przykład, próba obrobienia znacznika czasu TIMESTAMP wzorcem dla DATE. Tip: upewnij się, że typy danych, których używasz, pasują do twoich potrzeb.

Funkcje TO_CHAR() i TO_DATE() dają mega możliwości do pracy z danymi czasowymi. Możesz robić wygodne formaty do raportów, konwertować dane od użytkownika i sprawiać, że twoje zapytania SQL będą bardziej czytelne. W realu te funkcje są szeroko używane do wizualizacji danych, robienia raportów, integracji z innymi systemami i budowania interfejsów użytkownika.