Data i czas w SQLite: strftime, date(), datetime() i modyfikatory

SQLite tak naprawdę nie ma typu daty

To zaskakuje każdego, kto przychodzi z Postgres lub MySQL. SQLite ma pięć klas przechowywania (NULL, INTEGER, REAL, TEXT, BLOB) i na tym koniec. Nie ma DATE, DATETIME ani TIMESTAMP. Możesz napisać created_at DATETIME w CREATE TABLE i SQLite to przyjmie, ale zapisze wartość jako zwykły tekst lub liczbę.

SQLite daje natomiast zestaw funkcji, które rozumieją trzy przyjęte formaty:

• Tekst ISO 8601: '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Znacznik czasu Unix: sekundy od 1970-01-01 UTC, zapisane jako liczba całkowita.
• Numer dnia juliańskiego: ułamkowe dni od 4714 r. p.n.e., zapisane jako liczba rzeczywista.

Wybierz jeden i się go trzymaj. Tekst ISO 8601 jest najczytelniejszy i poprawnie sortuje się jako ciąg znaków, dlatego jest domyślny.

SELECT date('now'),
       datetime('now'),
       strftime('%s', 'now'),
       julianday('now');

Cztery sposoby zapytania o "teraz": data tekstowa, data i czas jako tekst, sekundy Unix i dzień juliański. Wszystkie opisują ten sam moment.

Pięć funkcji dat

SQLite ma pięć wbudowanych funkcji, które obejmują prawie wszystko:

• date(time, ...): zwraca YYYY-MM-DD.
• time(time, ...): zwraca HH:MM:SS.
• datetime(time, ...): zwraca YYYY-MM-DD HH:MM:SS.
• julianday(time, ...): zwraca liczbę rzeczywistą (świetną do różnic).
• strftime(format, time, ...): zwraca ciąg w dowolnym formacie.

Każda przyjmuje wartość czasu jako pierwszy argument, a potem dowolną liczbę ciągów modyfikatorów.

SELECT date('2026-04-23 10:15:00')          AS just_date,
       time('2026-04-23 10:15:00')          AS just_time,
       datetime(1745402100, 'unixepoch')    AS from_unix,
       julianday('2026-04-23')              AS julian;

Zwróć uwagę na 'unixepoch': w ten sposób mówisz funkcjom dat, że wejściowa liczba całkowita to znacznik czasu Unix, a nie dzień juliański. Bez tego SQLite zakłada, że liczba jest dniem juliańskim.

strftime: własne formaty dat

strftime to koń pociągowy. Kody z % są takie same jak w C czy Pythonie:

SELECT strftime('%Y-%m-%d',           'now') AS iso_date,
       strftime('%d/%m/%Y',           'now') AS european,
       strftime('%H:%M',              'now') AS hours_minutes,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_with_ms,
       strftime('%w',                 'now') AS day_of_week,
       strftime('%j',                 'now') AS day_of_year;

Kody, po które sięga się najczęściej:

• %Y: rok czterocyfrowy.
• %m: miesiąc (01-12).
• %d: dzień miesiąca (01-31).
• %H, %M, %S: godziny, minuty, sekundy.
• %w: dzień tygodnia (0 = niedziela).
• %j: dzień roku (001-366).
• %s: znacznik czasu Unix.
• %f: sekundy z częścią ułamkową (SS.SSS).

strftime służy też do wyciągania części daty, bo nie ma osobnej funkcji EXTRACT ani YEAR(). Po prostu formatujesz datę do potrzebnej części i rzutujesz, jeśli potrzebujesz liczby:

SELECT strftime('%Y', '2026-04-23') AS year_text,
       CAST(strftime('%Y', '2026-04-23') AS INTEGER) AS year_num,
       CAST(strftime('%m', '2026-04-23') AS INTEGER) AS month_num;

strftime zawsze zwraca tekst, więc opakuj go w CAST(... AS INTEGER), gdy chcesz liczyć albo porównywać liczbowo.

Modyfikatory: arytmetyka dat bez operatora

To funkcja, dzięki której praca z datami w SQLite jest przyjemna. Po argumencie czasu możesz przekazać dowolną liczbę ciągów modyfikatorów, które są stosowane po kolei:

SELECT date('2026-04-23', '+7 days')              AS week_later,
       date('2026-04-23', '-1 month')             AS month_earlier,
       datetime('now', '+2 hours', '+30 minutes') AS in_two_thirty,
       date('now', 'start of month')              AS first_of_month,
       date('now', 'start of month', '+1 month', '-1 day') AS last_of_month,
       date('now', 'weekday 0')                   AS next_sunday;

Modyfikatory, których będziesz używać bez przerwy:

• '+N days', '-N days' i to samo dla hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year': przycięcie do tej granicy.
• 'weekday N': przejście do najbliższego podanego dnia tygodnia (0 = niedziela).
• 'localtime' i 'utc': przeliczanie między strefami czasowymi.

Sztuczkę z "ostatnim dniem miesiąca" (początek miesiąca, plus jeden miesiąc, minus jeden dzień) warto zapamiętać. SQLite nie ma funkcji LAST_DAY, ale łańcuch modyfikatorów daje to samo.

UTC a czas lokalny

'now' zawsze zwraca UTC. Jeśli chcesz czasu lokalnego, musisz o to poprosić:

SELECT datetime('now')              AS utc_now,
       datetime('now', 'localtime') AS local_now,
       datetime('2026-04-23 10:00:00', 'utc') AS treated_as_local_then_utc;

Modyfikator 'localtime' przelicza wartość UTC na lokalną strefę systemu. Modyfikator 'utc' robi odwrotnie: traktuje wejście jako czas lokalny i przelicza na UTC.

Bezpieczny nawyk: przechowuj wszystko w UTC, a na czas lokalny przeliczaj tylko przy wyświetlaniu. Mieszanie stref w zapisanych danych prowadzi do błędów, które wychodzą dwa razy w roku przy zmianie czasu.

Porównywanie dat i filtrowanie zakresów

Jeśli zapisujesz daty jako tekst ISO 8601, porównania i BETWEEN po prostu działają: ISO 8601 sortuje się leksykograficznie tak samo jak chronologicznie. To właśnie dlatego jest to format domyślny.

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL  -- ISO 8601
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00');

SELECT customer, placed_at
FROM orders
WHERE placed_at >= '2026-04-01'
  AND placed_at <  '2026-05-01'
ORDER BY placed_at;

Zakres jednostronnie otwarty (>= start, < end) to przydatny nawyk: całkowicie eliminuje pytanie "czy północ 30. dnia się wliczyła, czy nie?".

Dla "ostatnich 7 dni" pozwól SQLite obliczyć granicę:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer, placed_at
FROM orders
WHERE placed_at >= datetime('now', '-7 days')
ORDER BY placed_at DESC;

Różnice dat

SQLite nie ma DATEDIFF. Wszystko obejmują dwa wzorce:

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS days_between,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS hours_between,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS seconds_between;

Różnice julianday() są w dniach (z dokładnością ułamkową), więc mnożenie przez 24 daje godziny, a przez 1440 minuty. Różnice strftime('%s', ...) są w sekundach, co wygodne, gdy chcesz liczby całkowitej.

CAST(... AS INTEGER) obcina ułamki dni, jeśli chcesz liczby pełnych dni:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer,
       CAST(julianday('now') - julianday(placed_at) AS INTEGER) AS days_ago
FROM orders
ORDER BY days_ago;

Przechowywanie dat: wybierz jeden format i się go trzymaj

Trzy rozsądne wybory, w kolejności od najczęściej zalecanego:

1. Tekst ISO 8601 (TEXT). Czytelny w zrzutach, poprawnie się sortuje i dobrze współpracuje z każdą funkcją dat. Wybór domyślny.
2. Sekundy Unix (INTEGER). Zwięzłe, szybkie porównania, bez niejednoznaczności stref czasowych. Dobre, gdy masz miliony wierszy. Do odczytu potrzebne jest datetime(col, 'unixepoch').
3. Dzień juliański (REAL). Rzadko się opłaca, chyba że wykonujesz dużo arytmetyki na datach i chcesz precyzji poniżej sekundy w jednej kolumnie.

Czego nie należy robić, to mieszać formatów w jednej kolumnie. Funkcje dat po cichu przyjmą każdy, ale indeksy, sortowanie i porównania dadzą bzdurne wyniki.

-- Wartości domyślne wypełniane przy wstawianiu:
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup'), ('login');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) to odpowiednik DEFAULT CURRENT_TIMESTAMP w SQLite: oznacza każdy nowy wiersz bieżącym czasem UTC bez udziału kodu aplikacji.

Grupowanie według okresów

strftime błyszczy, gdy chcesz pogrupować wiersze według miesiąca, tygodnia lub godziny:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00'),
    ('Ezra',  '2026-05-20 17:00:00');

SELECT strftime('%Y-%m', placed_at) AS month,
       COUNT(*)                     AS order_count
FROM orders
GROUP BY month
ORDER BY month;

Ten sam pomysł działa dla "zamówień według godziny dnia", "rejestracji według dnia tygodnia" czy "zdarzeń na minutę": wybierz ciąg formatu, który zachowuje tylko potrzebną ci szczegółowość, pogrupuj według niego i zagreguj.

Dalej: funkcje agregujące

Skoro mowa o grupowaniu: to COUNT(*) to najprostsza z funkcji agregujących SQLite. Następnie przyjrzymy się całemu zestawowi: SUM, AVG, MIN, MAX i temu, jak zamieniają wiele wierszy w jedną wartość podsumowania.