Come usare i timestamp Unix per query su intervalli di date

Interrogare i record dentro un intervallo di date — “mostrami tutto degli ultimi 30 giorni”, “trova gli ordini tra il 1° gennaio e il 31 marzo” — è una delle operazioni più comuni in qualsiasi applicazione con database. Il modo in cui salvi e interroghi i timestamp ha un impatto enorme su quanto queste query siano semplici e affidabili.

I timestamp Unix (interi che rappresentano i secondi trascorsi dall’epoch Unix) sono spesso la scelta migliore, ma solo se sai usarli nel modo giusto. Ecco la guida pratica.

Perché i timestamp Unix rendono le query per intervalli più semplici

Quando i timestamp sono salvati come interi, una query su intervallo diventa un semplice confronto numerico:

SELECT * FROM events
WHERE created_at >= 1704067200
  AND created_at < 1706745600;

Fine. Nessun parsing di stringhe, nessuna conversione di fuso orario, niente “semantica” ambigua di BETWEEN. Il database confronta due interi.

Confrontalo con un approccio basato su stringhe di datetime formattate:

SELECT * FROM events
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-02-01 00:00:00';

Sembra simile, ma introduce diverse domande: in che fuso orario è '2024-01-01 00:00:00'? Il server applicativo lo interpreta allo stesso modo del server del database? Cosa succede quando utenti in fusi orari diversi eseguono la stessa query?

I timestamp Unix evitano tutto questo. Sono sempre in UTC, sempre non ambigui, sempre un numero. La conversione in ora locale avviene nello strato di presentazione, non nella query.

Calcolare i timestamp per gli intervalli di date più comuni

Per scrivere una query su intervallo, ti servono i timestamp Unix dell’inizio e della fine dell’intervallo. Il convertitore di timestamp Unix lo rende semplice: inserisci la data e ottieni il timestamp.

Ecco i calcoli per gli schemi più comuni:

Ultimi N giorni: ` start = current_timestamp - (N × 86400) end = current_timestamp `

Ultimi 7 giorni: start = now - 604800 (7 × 86.400 secondi) Ultimi 30 giorni: start = now - 2592000 (30 × 86.400) Ultimi 90 giorni: start = now - 7776000

Un mese di calendario specifico (es. marzo 2024): ` start = Unix timestamp di 2024-03-01 00:00:00 UTC = 1709251200 end = Unix timestamp di 2024-04-01 00:00:00 UTC = 1711929600 `

Query: WHERE created_at >= 1709251200 AND created_at < 1711929600

Nota: usa < (minore di) invece di <= per il limite finale quando usi la mezzanotte del giorno successivo. Così escludi in modo pulito qualsiasi record del 1° aprile senza doverti preoccupare dell’ultimo secondo esatto del 31 marzo.

Da inizio anno (year to date): ` start = Unix timestamp del 1° gennaio dell’anno corrente, 00:00:00 UTC end = current timestamp `

Intervallo fisso tra due date: Converti entrambe le date in timestamp di mezzanotte UTC con il convertitore e usale direttamente come limiti.

Gestire correttamente i fusi orari

L’errore più comune con le query per intervalli basate su timestamp Unix è la confusione sui fusi orari quando generi i timestamp di confine.

I timestamp Unix sono sempre in UTC. Se la tua applicazione deve interrogare “ordini effettuati l’8 aprile in orario di New York (UTC-4)”, allora l’8 aprile a New York copre:

2024-04-08 00:00:00 EDT = 2024-04-08 04:00:00 UTC = Unix timestamp 1712545200
2024-04-09 00:00:00 EDT = 2024-04-09 04:00:00 UTC = Unix timestamp 1712631600

Se usi ingenuamente la mezzanotte UTC invece:

2024-04-08 00:00:00 UTC = Unix timestamp 1712534400

Finiresti per includere 4 ore di record della tarda serata del 7 aprile (ora di New York) e perderesti 4 ore della tarda serata dell’8 aprile.

L’approccio corretto: converti sempre l’intervallo in UTC prima di generare i timestamp di confine. In codice:

import datetime, pytz

tz = pytz.timezone('America/New_York')
start_local = tz.localize(datetime.datetime(2024, 4, 8, 0, 0, 0))
start_utc_ts = int(start_local.utctimetuple().tm_sec)  # or .timestamp()

Oppure usa una libreria che gestisce timestamp “timezone-aware”. Il principio chiave: genera i limiti nel fuso orario dell’utente, converti in UTC, poi usa l’intero risultante nella query.

Indicizzazione per le prestazioni

Perché le query su intervalli di timestamp siano veloci, la colonna timestamp deve avere un indice. Su una tabella con milioni di righe, una scansione non indicizzata su un intervallo sarà lenta, indipendentemente da quanto “semplice” sembri la query.

PostgreSQL: `sql CREATE INDEX idx_events_created_at ON events (created_at); `

MySQL: `sql ALTER TABLE events ADD INDEX idx_created_at (created_at); `

Per query che combinano spesso intervalli di timestamp con altri filtri (es. WHERE user_id = 123 AND created_at >= X), un indice composito con la colonna più selettiva per prima di solito è più veloce:

CREATE INDEX idx_events_user_created ON events (user_id, created_at);

Gli indici compositi permettono al database di restringere prima per utente, poi di scansionare solo i record di quell’utente ordinati per tempo dentro l’intervallo.

Generare i limiti di timestamp nei linguaggi più comuni

JavaScript: `javascript const now = Math.floor(Date.now() / 1000); const last30Days = now - (30 * 86400); // Query: WHERE created_at >= last30Days AND created_at <= now `

Python: `python import time now = int(time.time()) last_30_days = now - (30 * 86400) `

Per una data specifica in UTC: `python import datetime dt = datetime.datetime(2024, 1, 1, tzinfo=datetime.timezone.utc) ts = int(dt.timestamp()) # 1704067200 `

PHP: `php $now = time(); $start = mktime(0, 0, 0, 1, 1, 2024); // Jan 1 2024 midnight local time // Use strtotime for UTC: strtotime('2024-01-01 00:00:00 UTC') `

SQL (PostgreSQL) — generare i limiti inline: `sql SELECT * FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days')::int AND created_at <= EXTRACT(EPOCH FROM NOW())::int; `

Errori comuni da evitare

Usare la mezzanotte locale invece della mezzanotte UTC. Se il tuo server applicativo è in un fuso orario diverso da UTC, new Date().setHours(0,0,0,0) in JavaScript ti dà la mezzanotte del fuso locale del server — non UTC. Sii sempre esplicito sul fuso.

Off-by-one sui limiti finali. created_at <= 1711929599 (ultimo secondo del 31 marzo) e created_at < 1711929600 (mezzanotte del 1° aprile) sono equivalenti, ma la seconda forma è più pulita e evita casi limite sotto il secondo se un giorno passi ai millisecondi.

Dimenticare le transizioni DST. Quando converti intervalli in fusi con ora legale, un giorno all’anno è di 23 ore e uno è di 25 ore. Questo influisce sui calcoli “ultime 24 ore”: now - 86400 non è sempre “ieri a quest’ora” in ora locale. Per query precise sul giorno locale, genera i limiti a partire dalla data di calendario locale, invece di sottrarre conteggi fissi di secondi.

Non indicizzare la colonna timestamp. Ovvia in teoria, ma spesso trascurata nelle fasi iniziali quando le tabelle sono piccole e le query lente non si notano ancora.

Usa il convertitore di timestamp Unix per fare un sanity check dei tuoi timestamp di confine prima di mettere in produzione una query: incolla l’intero e verifica che si converta nella data e ora che ti aspetti.

Install this app