Fusi orari e timestamp

I fusi orari sono il punto in cui anche i candidati più sicuri inciampano, quindi gli intervistatori li verificano per valutare il livello di comprensione. La domanda fondamentale è sempre: "come si memorizzano e confrontano i timestamp tra regioni diverse?"

La risposta professionale consiste in una disciplina, non in una funzione: memorizzare tutto in UTC e convertire solo ai margini, per la visualizzazione. Se il modello di memorizzazione è corretto, la maggior parte delle query diventa banale.

• timestamp vs timestamptz
• Conversione tra fusi orari
• UTC come fonte di verità

PostgreSQL dispone di due tipi di timestamp e confonderli è uno degli errori più comuni nei colloqui.

• timestamp (senza fuso orario): un valore dell'orologio locale con nessun fuso associato. Memorizza esattamente il valore fornito.
• timestamptz (con fuso orario): viene memorizzato internamente come UTC; in input viene convertito dal fuso della sessione, mentre in output viene riconvertito.

Nonostante il nome, timestamptz non memorizza un fuso; memorizza un istante preciso in UTC. È un dettaglio che fa una buona impressione agli intervistatori.

La regola d'oro. Si memorizzano gli istanti in UTC (usando timestamptz) e si converte in un fuso locale solo quando si presenta il valore a un utente. In questo modo si evitano le ambiguità legate all'ora legale e l'ordinamento temporale è corretto ovunque.

Se viene chiesto "perché UTC?", si può rispondere: UTC non subisce cambiamenti dovuti all'ora legale, quindi lo stesso valore dell'orologio locale non si verifica mai due volte né viene saltato, a differenza dell'ora locale.

AT TIME ZONE è un costrutto ingegnoso e una fonte frequente di errori, perché esegue due operazioni opposte a seconda del tipo dell'input:

• Applicato a un timestamptz, converte l'istante assoluto nel fuso indicato e restituisce un timestamp semplice, cioè l'ora visualizzata in quel fuso.
• Applicato a un timestamp semplice, interpreta quell'ora locale come appartenente al fuso indicato e restituisce un timestamptz.

Il punto fondamentale è capire in quale direzione opera.

È importante conoscere le funzioni che restituiscono l'istante corrente. NOW() e CURRENT_TIMESTAMP restituiscono un timestamptz in Postgres. Restituiscono l'ora dell'inizio della transazione, non quella dell'istruzione, un aspetto importante nelle transazioni lunghe.

Per ottenere esplicitamente UTC, esegua la conversione: NOW() AT TIME ZONE 'UTC'. In MySQL, UTC_TIMESTAMP() restituisce direttamente l'ora UTC.

Chi conduce i colloqui ama i casi limite dell'ora legale. Quando gli orologi avanzano, un'ora dell'orologio locale non esiste; quando tornano indietro, un'ora si ripete. Memorizzare l'ora locale rende questi orari ambigui o non validi.

Memorizzare UTC evita completamente il problema: ogni istante è univoco e monotono. Indicare una regione come 'America/New_York', invece di un offset fisso come -05:00, consente al database di applicare correttamente le regole dell'ora legale per qualsiasi data.

Un problema realistico: «utenti attivi giornalmente nel fuso locale di ciascun utente». Se si tronca direttamente il timestamp UTC, i confini della mezzanotte sono errati per gli utenti che non si trovano in UTC.

Converta il timestamp nel fuso dell'utente prima di troncarlo al giorno. La conversione sposta l'ora locale in modo che i confini del giorno siano allineati localmente.

Quando si filtra una colonna timestamptz, la si confronti con un istante esplicito, idealmente un valore letterale UTC o un timestamptz con offset. Il confronto con una stringa priva di indicazioni può essere interpretato nel fuso imprevedibile della sessione.

In questo modo il confronto resta non ambiguo, indipendentemente da chi esegue la query.

Molti sistemi memorizzano l'ora come Unix epoch, cioè i secondi trascorsi dal 1970-01-01 UTC. In un colloquio potrebbe ricevere una colonna intera e le potrebbe essere chiesto di interpretarla.

• Postgres: TO_TIMESTAMP(epoch_seconds) restituisce un timestamptz.
• Per tornare all'epoch: EXTRACT(EPOCH FROM occurred_at).
• MySQL: FROM_UNIXTIME() e UNIX_TIMESTAMP().

I valori epoch sono intrinsecamente UTC, anche per questo sono molto usati per la memorizzazione.

Una mappa rapida per dimostrare padronanza in qualsiasi ambiente:

• Postgres: timestamptz e AT TIME ZONE, con il supporto più completo.
• MySQL: TIMESTAMP esegue la conversione automatica tramite time_zone della sessione; CONVERT_TZ(t, from, to) esegue una conversione esplicita. DATETIME non tiene conto del fuso orario.
• SQL Server: datetimeoffset memorizza un offset; AT TIME ZONE 'name' converte usando i nomi dei fusi di Windows.

Una domanda di reporting sottile: contare le sessioni per giorno del calendario locale quando una sessione può attraversare la mezzanotte. La soluzione richiede la stessa disciplina: convertire all'ora locale e poi suddividere in intervalli.

Memorizzi l'inizio e la fine come timestamptz; per il reporting, ricavi il giorno locale dall'inizio convertito. Se una sessione deve essere suddivisa tra due giorni, dovrebbe eseguire un join con una sequenza di giorni, un ottimo punto da sollevare come approfondimento.

Confermi la strategia di memorizzazione consigliata e la motivazione.

La disciplina da portare con sé:

• Memorizzi UTC come timestamptz; converta in un fuso denominato solo per la visualizzazione.
• timestamptz memorizza un istante UTC, non un fuso orario, nonostante il nome.
• AT TIME ZONE opera in entrambe le direzioni a seconda del tipo dell'input: converte un timestamptz nell'ora locale oppure interpreta un timestamp semplice come appartenente a un fuso.
• Utilizzi nomi di regioni come 'America/New_York', così l'ora legale viene applicata automaticamente; eviti gli offset fissi.
• Converta all'ora locale prima di troncare al giorno e confronti le colonne con istanti UTC espliciti.