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

MongoDB stores dates natively as the BSON Date type — a signed 64-bit integer of milliseconds since the Unix epoch. The driver maps it to a Date object in JavaScript, datetime in Python, ISODate in the mongo shell.

• BSON Date: 8 bytes, signed 64-bit, milliseconds since 1970 UTC
• Indexes natively — range queries on Date are fast and idiomatic
• Use ISODate("2024-01-01T00:00:00Z") in the shell to create comparable values
• For multi-precision needs, use Decimal128 or a sub-document with seconds + nanos; Date itself is millisecond precision
• TTL indexes operate on Date fields specifically — integer epoch fields will not work with expireAfterSeconds

• Convertire valori epoch del database
• Il problema dell'anno 2038
• Guida secondi vs millisecondi
• 7 bug di timestamp da evitare
• Snippet di timestamp SQL

DynamoDB: epoch attributes and TTL

DynamoDB has no dedicated date type. Two patterns dominate: ISO 8601 strings for human readability, or epoch numbers for compactness and TTL support.

• TTL requires the attribute to be a Number representing Unix seconds (not milliseconds)
• Strings store ISO 8601 with full timezone info but cost more bytes and sort lexicographically (so include the Z suffix consistently)
• Range queries on a sort-key epoch number are extremely cheap and align with DynamoDB’s query model
• Avoid mixing seconds and milliseconds across tables — DynamoDB will not protect you from the unit drift
• DynamoDB Streams emit an ApproximateCreationDateTime in ISO 8601 regardless of how you stored the original value