Date e orari in SQLite: strftime, date(), datetime() e modificatori

SQLite non ha davvero un tipo per le date

Questo spiazza chiunque arrivi da Postgres o MySQL. SQLite ha cinque classi di archiviazione, NULL, INTEGER, REAL, TEXT, BLOB, e basta. Non esiste DATE, né DATETIME, né TIMESTAMP. Puoi scrivere created_at DATETIME nel tuo CREATE TABLE, e SQLite lo accetta, ma salva il valore come semplice testo o come numero.

Quello che SQLite ti dà è un insieme di funzioni che capiscono tre formati convenzionali:

• Testo ISO 8601: '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Timestamp Unix: secondi dal 1970-01-01 UTC, salvati come intero.
• Numero del giorno giuliano: giorni frazionari dal 4714 a.C., salvati come real.

Scegline uno e resta su quello. Il testo ISO 8601 è il più leggibile e si ordina correttamente come stringa, ed è per questo che è il formato predefinito.

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

Quattro modi per chiedere "adesso": data in testo, data e ora in testo, secondi Unix, giorno giuliano. Descrivono tutti lo stesso istante.

Le cinque funzioni per le date

SQLite ti dà cinque funzioni integrate che coprono quasi tutto:

• date(time, ...): restituisce YYYY-MM-DD.
• time(time, ...): restituisce HH:MM:SS.
• datetime(time, ...): restituisce YYYY-MM-DD HH:MM:SS.
• julianday(time, ...): restituisce un real (ottimo per le differenze).
• strftime(format, time, ...): restituisce una stringa con un formato personalizzato.

Ognuna prende un valore temporale come primo argomento, poi un numero qualsiasi di stringhe modificatore.

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;

Nota 'unixepoch': è il modo per dire alle funzioni per le date che l'intero in ingresso è un timestamp Unix e non un giorno giuliano. Senza, SQLite presume che il numero sia giuliano.

strftime: formati di data personalizzati

strftime è il cavallo da tiro. Usa gli stessi codici % che conosci da C o Python:

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;

I codici che userai più spesso:

• %Y: anno a quattro cifre.
• %m: mese (01-12).
• %d: giorno del mese (01-31).
• %H, %M, %S: ore, minuti, secondi.
• %w: giorno della settimana (0=domenica).
• %j: giorno dell'anno (001-366).
• %s: timestamp Unix.
• %f: secondi con frazioni (SS.SSS).

strftime è anche il modo per estrarre parti di una data: non esiste una funzione EXTRACT o YEAR() separata. Formatti la data fino alla parte che ti interessa e fai il cast se ti serve un numero:

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 restituisce sempre testo, quindi racchiudilo in CAST(... AS INTEGER) quando vuoi fare calcoli o confronti numerici.

Modificatori: calcoli sulle date senza operatori

È la funzionalità che rende piacevole la gestione delle date in SQLite. Dopo l'argomento temporale puoi passare un numero qualsiasi di stringhe modificatore, che vengono applicate in ordine:

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;

I modificatori che userai di continuo:

• '+N days', '-N days', e lo stesso per hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year': troncano a quel limite.
• 'weekday N': avanza al prossimo giorno della settimana indicato (0=domenica).
• 'localtime' e 'utc': convertono tra fusi orari.

Il trucco dell'"ultimo giorno del mese", inizio del mese, più un mese, meno un giorno, vale la pena di memorizzarlo. SQLite non ha una funzione LAST_DAY, ma concatenare i modificatori ti dà lo stesso risultato.

UTC contro ora locale

'now' restituisce sempre l'ora UTC. Se vuoi l'ora locale, devi chiederla:

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;

Il modificatore 'localtime' converte un valore UTC nel fuso locale del sistema. Il modificatore 'utc' fa il contrario: tratta l'input come ora locale e lo converte in UTC.

L'abitudine sicura: salva tutto in UTC e converti in ora locale solo al momento di mostrarlo. Mescolare fusi orari nei dati salvati porta a bug che si presentano due volte l'anno, al cambio dell'ora legale.

Confrontare date e filtrare intervalli

Se salvi le date come testo ISO 8601, i confronti e BETWEEN funzionano e basta: l'ordine lessicografico di ISO 8601 coincide con quello cronologico. È proprio per questo che è il formato predefinito.

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;

L'intervallo semiaperto (>= start, < end) è un'abitudine utile: evita del tutto la domanda "la mezzanotte del 30 è inclusa o no?".

Per "gli ultimi 7 giorni", lascia che sia SQLite a calcolare il limite:

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;

Differenze tra date

SQLite non ha DATEDIFF. Due schemi coprono tutto:

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;

Le differenze tra julianday() sono in giorni (con precisione frazionaria), quindi moltiplicare per 24 dà le ore, per 1440 i minuti. Le differenze tra strftime('%s', ...) sono in secondi: comode quando vuoi un intero.

CAST(... AS INTEGER) tronca le frazioni di giorno se vuoi contare giorni interi:

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;

Salvare le date: scegli un formato e mantienilo

Tre scelte ragionevoli, in ordine di quanto spesso dovresti sceglierle:

1. Testo ISO 8601 (TEXT). Leggibile nei dump, si ordina correttamente, funziona bene con ogni funzione per le date. La scelta predefinita.
2. Secondi Unix (INTEGER). Compatto, confronti veloci, nessuna ambiguità di fuso orario. Ottimo quando hai milioni di righe. Ti servirà datetime(col, 'unixepoch') per rileggerlo.
3. Giorno giuliano (REAL). Raramente conviene, a meno che tu non faccia calcoli pesanti sulle date e voglia una precisione inferiore al secondo in una sola colonna.

Quello che non dovresti fare è mescolare formati nella stessa colonna. Le funzioni per le date accetteranno l'uno e l'altro senza dire nulla, ma indici, ordinamenti e confronti produrranno risultati senza senso.

-- Valori di default che si compilano da soli all'inserimento:
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')) è l'equivalente SQLite di DEFAULT CURRENT_TIMESTAMP: marca ogni nuova riga con l'ora UTC corrente, senza bisogno di codice applicativo.

Raggruppare per periodo di tempo

strftime dà il meglio quando vuoi raggruppare le righe per mese, settimana o ora:

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;

Stessa idea per "ordini per ora del giorno", "iscrizioni per giorno della settimana" o "eventi al minuto": scegli una stringa di formato che catturi solo la granularità che ti interessa, raggruppa su quella e aggrega.

Prossimo passo: le funzioni di aggregazione

A proposito di raggruppamenti: quel COUNT(*) è la più semplice delle funzioni di aggregazione di SQLite. Nella prossima pagina vedremo l'insieme completo: SUM, AVG, MIN, MAX e come riducono molte righe a un unico valore di riepilogo.