Memorizzare i timestamp nei database: DATETIME vs INT vs BIGINT

Tre modi per memorizzare un timestamp

La maggior parte dei database offre almeno tre opzioni: un tipo datetime nativo (TIMESTAMP, DATETIME, TIMESTAMPTZ), un intero semplice (INT o BIGINT) o una stringa (VARCHAR). Ognuno ha compromessi diversi su dimensione di archiviazione, ergonomia delle query, gestione dei fusi e durata nel tempo. Per la maggior parte dei database di prodotto, una colonna datetime nativa è il miglior default perché il database può confrontare, indicizzare, troncare, raggruppare e formattare il valore come tempo invece che come un numero anonimo.

• Tipo datetime nativo — il migliore per aritmetica di date, conversione di fuso e leggibilità
• Intero BIGINT — buono per insert ad alto throughput e query numeriche di intervallo semplici
• Stringa VARCHAR — quasi sempre sbagliato: il confronto tra stringhe di date funziona solo con formato ISO 8601 rigoroso
• Intero INT — da evitare per i timestamp futuri a meno che tu non abbia verificato a fondo il limite dell'anno 2038

MySQL: TIMESTAMP vs DATETIME vs INT

MySQL ha due tipi data-ora che sembrano simili ma si comportano in modo molto diverso — e uno di essi ha una data di scadenza rigida. TIMESTAMP è comodo quando vuoi la conversione automatica tra UTC e il fuso di sessione, ma il suo intervallo storico a 32 bit lo rende rischioso per dati di prodotto orientati al futuro. DATETIME memorizza la data e l'ora letterali che fornisci, di solito più chiaro quando l'applicazione standardizza su UTC prima di scrivere.

• TIMESTAMP: memorizzato internamente come secondi Unix a 32 bit — limitato da 1970-01-01 a 2038-01-19
• TIMESTAMP: converte automaticamente tra UTC e il fuso di sessione all'insert/lettura
• DATETIME: memorizza la data-ora letterale, senza fuso. Intervallo 1000-01-01 a 9999-12-31. Non interessato dal Y2038.
• DATETIME: non converte i fusi — controlli UTC a livello applicativo
• Raccomandazione: usa DATETIME con valori UTC espliciti per le nuove tabelle per evitare il limite del 2038

PostgreSQL: TIMESTAMPTZ è la scelta giusta

Il TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) di PostgreSQL memorizza i timestamp come microsecondi UTC internamente e li converte nel fuso di sessione in output. È l'opzione più sicura e corretta per la maggior parte dei casi perché rappresenta un istante reale nel tempo. Il nome può ingannare: TIMESTAMPTZ non memorizza l'etichetta del fuso originale come America/New_York. Memorizza l'istante, poi lo visualizza secondo il fuso di sessione corrente.

• TIMESTAMPTZ: memorizza UTC, converte nel fuso di sessione in output — portabile e sicuro rispetto all'ora legale
• TIMESTAMP (senza fuso): memorizza il valore letterale senza conversione — usalo solo per dati senza fuso
• EXTRACT(EPOCH FROM col): restituisce i secondi Unix come float da qualsiasi colonna TIMESTAMP
• TO_TIMESTAMP(epoch): converte i secondi Unix di nuovo in un TIMESTAMPTZ

Indicizzazione e prestazioni delle query

Per le tabelle applicative normali, la differenza di prestazioni tra colonne datetime native e colonne epoch BIGINT è raramente il fattore decisivo. La forma della query, il design dell'indice, il partizionamento e il numero di righe contano di più. Scegli prima il tipo che mantiene il significato corretto, poi indicizzalo per le query di intervallo che la tua applicazione esegue davvero.

• Tutti e tre i tipi supportano indici B-tree e query di intervallo efficienti
• Gli interi BIGINT sono marginalmente più veloci per scansioni di uguaglianza e intervallo su tabelle ad altissimo volume
• I tipi datetime nativi consentono query indicizzate per parte di data: WHERE created_at::date = '2024-01-01'
• I timestamp VARCHAR sono i peggiori per le prestazioni — il confronto tra stringhe non capisce le date

Quando ha senso l'archiviazione epoch in BIGINT

BIGINT è ragionevole quando i dati sono di tipo evento, con molti insert, e già prodotti come tempo Unix da un altro sistema. Pipeline di analytics, flussi di telemetria, code e protocolli binari compatti usano spesso millisecondi epoch perché i valori numerici sono veloci da confrontare e neutrali rispetto al linguaggio. Il compromesso è la leggibilità: gli umani hanno bisogno di un convertitore e l'aritmetica di date in SQL diventa più verbosa.

• Usa BIGINT per i millisecondi Unix se i client JavaScript producono direttamente gli eventi
• Usa BIGINT per i secondi Unix se il sistema sorgente è in stile Unix e la precisione al secondo basta
• Documenta l'unità nel nome della colonna: created_at_ms è più chiaro di created_at_epoch
• Aggiungi una colonna datetime generata se gli analisti hanno bisogno di query SQL leggibili
• Evita INT per i timestamp moderni orientati al futuro a causa dei limiti di intervallo a 32 bit

Pattern di schema consigliati

Per la maggior parte delle applicazioni web, memorizza un istante in UTC e memorizza il fuso preferito dell'utente separatamente solo quando devi ricostruire l'intenzione di orologio locale. Una riunione fissata per le 9:00 America/New_York è diversa da un log di eventi creato in un preciso istante UTC; modella questi casi in modo diverso.

• Log di eventi: created_at TIMESTAMPTZ in PostgreSQL, o created_at DATETIME in UTC per MySQL
• Ingestione di eventi JavaScript: created_at_ms BIGINT più documentazione API chiara
• Pianificazioni locali ricorrenti: local_date, local_time e timezone_id, poi calcola il prossimo istante
• Timestamp di scadenza: expires_at come datetime nativo o expires_at_seconds con secondi Unix espliciti
• Tabelle di audit: mantieni created_at e updated_at come colonne datetime native per un debug leggibile

FAQ sui timestamp nei database

Devo memorizzare UTC o l'ora locale in un database?

Memorizza UTC per i timestamp degli eventi e converti in ora locale alla visualizzazione. Memorizza un identificatore di fuso separatamente quando conta l'intenzione di orologio locale dell'utente, come riunioni ricorrenti od orari di lavoro.

BIGINT è meglio di TIMESTAMP?

Non in generale. BIGINT è utile per pipeline numeriche di epoch, ma i tipi datetime nativi sono più facili per l'aritmetica di date in SQL, il debug leggibile e l'output consapevole del fuso.

MySQL dovrebbe usare TIMESTAMP o DATETIME?

Per le nuove tabelle applicative, DATETIME con valori UTC è spesso più sicuro perché evita il limite di intervallo del 2038 e non dipende silenziosamente dalla conversione di fuso di sessione.

Devo memorizzare i timestamp in UTC o con un fuso?

Memorizza l'istante in UTC (TIMESTAMPTZ in PostgreSQL, o DATETIME con valori UTC in MySQL) e converti in ora locale alla visualizzazione. Mantieni una colonna di fuso IANA separata solo quando devi ricostruire l'intenzione di orologio locale dell'utente, come riunioni ricorrenti.