Lavorare con i fusi orari: TIMEZONE

Mettiamo che hai un'app per prenotare voli. Un volo parte da New York alle 10:00 ora locale e arriva a Londra alle 22:00 ora locale. Se ignori i fusi orari, il tuo server può trasformare tutto in un casino totale, mostrando l'orario di arrivo sbagliato.

I fusi orari sono i tuoi migliori amici (o i peggiori nemici, quando qualcosa va storto). Se i tuoi utenti sono in paesi diversi o devi lavorare con orari che dipendono dall'ora locale (tipo orari dei voli o eventi), allora gestire i fusi orari diventa fondamentale.

Tipi di dati temporali

Abbiamo già parlato del fatto che ci sono due tipi di dati per lavorare con i timestamp:

• TIMESTAMP: data e ora senza considerare il fuso orario.
• TIMESTAMPTZ: data e ora con il fuso orario.

Vediamoli di nuovo con un esempio.

-- Creiamo una tabella con due colonne: TIMESTAMP e TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Inseriamo i dati
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Controlliamo i dati
SELECT * FROM flight_schedule;

Il risultato dipende dal fuso orario del tuo server. Per esempio:

La differenza chiave:

• La colonna departure_time salva solo data e ora senza nessun riferimento a un fuso orario.
• La colonna departure_time_with_tz salva data e ora insieme all'informazione sul fuso (+00 in questo caso).

Conversione dell'orario tra diversi fusi orari

Per lavorare con i fusi orari in PostgreSQL si usa la funzione AT TIME ZONE.

Convertire UTC in ora locale

Supponiamo di avere un timestamp in formato UTC (tempo coordinato universale). Vogliamo mostrarlo a un utente che si trova nel fuso America/New_York.

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

Risultato:

AT TIME ZONE qui funziona come una bacchetta magica: converte l'orario da UTC al fuso specificato.

Convertire l'ora locale in UTC

Ora immaginiamo il contrario: abbiamo un orario in America/New_York e vogliamo convertirlo in UTC.

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

Risultato:

Nota che il risultato sarà in formato TIMESTAMPTZ, perché include l'informazione sul fuso (in questo caso UTC).

Lavorare con il tipo di dato TIMESTAMPTZ

Quando lavori con TIMESTAMPTZ, PostgreSQL tiene conto automaticamente del fuso orario del tuo server (o di quello che hai impostato tu).

Puoi impostare il fuso orario per la sessione corrente con il comando:

SET TIMEZONE = 'Europe/Istanbul';

Dopo questo, tutte le operazioni con TIMESTAMPTZ useranno questo fuso orario.

Esempio: inserimento e selezione dati

-- Impostiamo il fuso orario
SET TIMEZONE = 'Europe/Istanbul';

-- Inseriamo i dati
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Controlliamo i dati
SELECT departure_time_with_tz FROM flight_schedule;

Risultato nel fuso Europe/Istanbul:

PostgreSQL converte automaticamente l'orario da UTC al fuso che hai impostato.

Esempi pratici

Gestione dei fusi orari per gli orari dei voli. Mettiamo che abbiamo una tabella con gli orari dei voli, dove ogni record salva l'orario di partenza in UTC. Vogliamo mostrare l'orario di partenza per ogni volo nell'ora locale.

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

Confronto di dati temporali da fusi diversi. Immagina di confrontare due eventi avvenuti in città diverse. PostgreSQL ti permette di farlo, portando automaticamente i dati allo stesso fuso.

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

Risultato:

Il confronto ha restituito true, perché 10:00+03 è uguale a 07:00+00.

Consigli e trappole comuni

Lavorare con le date e gli orari è sempre insidioso. Ecco cosa spesso va storto:

• Si usa TIMESTAMP invece di TIMESTAMPTZ e poi ci si chiede perché gli orari non coincidono — perché i fusi orari vengono ignorati.
• Non si sa in che fuso orario gira il server, e quindi i dati vengono inseriti in un orario e letti in un altro.
• Si sbaglia il nome del fuso orario usando AT TIME ZONE — e si ottiene un errore o l'orario sbagliato.

Per non cascarci:

• Quasi sempre usa TIMESTAMPTZ, soprattutto se i dati possono dipendere dal fuso orario.
• Salva l'orario in UTC e poi, quando mostri i dati, converti nel fuso dell'utente.
• Se vuoi approfondire, ecco la documentazione ufficiale di PostgreSQL su date e fusi orari — davvero utile.