Trabajando con zonas horarias: TIMEZONE

Imagina que tienes una app para reservar vuelos. Un vuelo sale de Nueva York a las 10:00 hora local y llega a Londres a las 22:00 hora local. Si no tienes en cuenta las zonas horarias, tu servidor puede montar un caos total y mostrar la hora de llegada mal.

Las zonas horarias son tus mejores colegas (o tus peores enemigos cuando todo sale mal). Si tus usuarios están en diferentes países o necesitas trabajar con horarios que dependen de la hora local (por ejemplo, horarios de vuelos o eventos), entonces tener en cuenta las zonas horarias es súper importante.

Tipos de datos de tiempo

Ya hemos hablado de que hay dos tipos de datos para trabajar con marcas de tiempo:

• TIMESTAMP: fecha y hora sin zona horaria.
• TIMESTAMPTZ: fecha y hora con zona horaria.

Vamos a repasarlos otra vez con un ejemplo.

-- Creamos una tabla con dos columnas: TIMESTAMP y TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Insertamos datos
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Comprobamos los datos
SELECT * FROM flight_schedule;

El resultado depende de la zona horaria de tu servidor. Por ejemplo:

Diferencia clave:

• La columna departure_time solo guarda la fecha y hora sin asociarla a ninguna zona horaria.
• La columna departure_time_with_tz guarda la fecha y hora junto con la info de la zona horaria (+00 en este caso).

Convertir horas entre zonas horarias

Para trabajar con zonas horarias en PostgreSQL se usa la función AT TIME ZONE.

Convertir UTC a hora local

Supón que tienes una marca de tiempo en UTC (hora universal coordinada). Queremos mostrarla a un usuario que está en la zona horaria America/New_York.

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

Resultado:

AT TIME ZONE aquí es como magia: convierte la hora de UTC a la zona horaria que le digas.

Convertir hora local a UTC

Ahora imagina lo contrario: tienes una hora en America/New_York y quieres convertirla a UTC.

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

Resultado:

Ojo, el resultado será en formato TIMESTAMPTZ, porque incluye la info de la zona horaria (UTC en este caso).

Trabajando con el tipo de dato TIMESTAMPTZ

Cuando usas TIMESTAMPTZ, PostgreSQL tiene en cuenta automáticamente la zona horaria de tu servidor (o la que tú le pongas).

Puedes establecer la zona horaria para la sesión actual con este comando:

SET TIMEZONE = 'Europe/Istanbul';

Después de esto, todas las operaciones con TIMESTAMPTZ se harán usando esa zona horaria.

Ejemplo: insertar y consultar datos

-- Ponemos la zona horaria
SET TIMEZONE = 'Europe/Istanbul';

-- Insertamos datos
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Comprobamos los datos
SELECT departure_time_with_tz FROM flight_schedule;

Resultado en la zona horaria Europe/Istanbul:

PostgreSQL convierte automáticamente la hora desde UTC usando la zona horaria que le diste.

Ejemplos prácticos

Tener en cuenta las zonas horarias en los horarios. Imagina que tienes una tabla con el horario de vuelos, donde cada registro guarda la hora de salida en UTC. Queremos mostrar la hora de salida para cada vuelo en la hora local.

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

Comparar datos de tiempo de diferentes zonas horarias. Imagina que comparamos dos eventos que pasaron en diferentes ciudades. PostgreSQL te deja hacerlo, convirtiendo los datos automáticamente a una sola zona horaria.

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

Resultado:

La comparación devuelve true, porque 10:00+03 es igual a 07:00+00.

Consejos y errores comunes

Trabajar con horas puede ser traicionero. Esto es lo que suele salir mal:

• Usan TIMESTAMP en vez de TIMESTAMPTZ y luego se preguntan por qué la hora no cuadra — porque las zonas horarias se ignoran.
• No saben en qué zona horaria está el servidor, y al final los datos se insertan en una hora y se leen en otra.
• Se equivocan en el nombre de la zona horaria al usar AT TIME ZONE — y sale un error o una hora incorrecta.

Para no liarla:

• Casi siempre usa TIMESTAMPTZ, sobre todo si los datos pueden depender de la zona horaria.
• Guarda la hora en UTC y solo al mostrarla conviértela a la zona horaria del usuario.
• Si quieres profundizar más, aquí tienes la documentación oficial de PostgreSQL sobre fechas y zonas horarias — muy útil.