0Pricing
SQL Interview Prep · Lección

Zonas horarias y marcas de tiempo

Almacenar en UTC, convertir zonas y conocer las preguntas habituales de las entrevistas sobre marcas de tiempo.

Zonas horarias y marcas de tiempo es una lección gratuita de SQL Interview Prep en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Interview Prep incluye 4 lecciones en total.

Por qué las zonas horarias hacen tropezar a los candidatos

Las zonas horarias son el punto en el que tropiezan los candidatos que parecían más seguros, por lo que los entrevistadores las utilizan para comprobar la profundidad de sus conocimientos. La pregunta fundamental siempre es: «¿cómo almacena y compara marcas de tiempo entre distintas regiones?»

La respuesta profesional es una disciplina, no una función: almacene todo en UTC y conviértalo solo en los extremos para mostrarlo. Si el modelo de almacenamiento es correcto, la mayoría de las consultas se vuelven triviales.

  • timestamp frente a timestamptz
  • Conversión entre zonas
  • UTC como fuente de verdad

timestamp frente a timestamptz

PostgreSQL tiene dos tipos de timestamp, y confundirlos es uno de los errores más habituales en una entrevista.

  • timestamp (sin zona horaria): un valor de reloj local sin ninguna zona asociada. Almacena exactamente lo que se le proporciona.
  • timestamptz (con zona horaria): se almacena internamente como UTC; al introducirlo, se convierte desde la zona de la sesión y, al mostrarlo, se convierte de nuevo.

A pesar de su nombre, timestamptz no almacena una zona; almacena un instante preciso en UTC. Este detalle suele impresionar a los entrevistadores.

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

Almacene en UTC y convierta en los extremos

La regla de oro. Guarde los instantes en UTC (utilice timestamptz) y conviértalos a una zona local únicamente cuando los presente al usuario. Esto evita la ambigüedad en torno al horario de verano y hace que el orden temporal sea correcto en cualquier lugar.

Si le preguntan «¿por qué UTC?», responda: UTC no tiene cambios por horario de verano, por lo que un mismo valor de reloj local nunca se repite ni se omite, a diferencia de la hora local.

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

El doble significado de AT TIME ZONE

AT TIME ZONE es una construcción ingeniosa y una fuente frecuente de errores porque hace dos cosas opuestas según el tipo de entrada:

  • Aplicado a un timestamptz, convierte el instante absoluto a esa zona y devuelve un timestamp simple (la hora local de esa zona).
  • Aplicado a un timestamp simple, interpreta esa hora local como perteneciente a esa zona y devuelve un timestamptz.

La clave está en saber en qué dirección funciona.

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

Obtener el instante actual

Conozca sus funciones para obtener el "ahora". NOW() y CURRENT_TIMESTAMP devuelven un timestamptz en Postgres. Devuelven la hora del inicio de la transacción, no la de la instrucción, lo cual es importante en transacciones largas.

Para obtener explícitamente la hora UTC, conviértala: NOW() AT TIME ZONE 'UTC'. En MySQL, UTC_TIMESTAMP() proporciona directamente la hora UTC.

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

El horario de verano es el verdadero enemigo

A los entrevistadores les encantan los casos límite del horario de verano. Cuando los relojes se adelantan, una hora local no existe; cuando se atrasan, una hora se repite. Almacenar la hora local hace que estos casos sean ambiguos o no válidos.

Almacenar la hora UTC evita completamente este problema: cada instante es único y monótono. Especificar una región como 'America/New_York' (en lugar de un desplazamiento fijo como -05:00) permite que la base de datos aplique correctamente las reglas del horario de verano para cualquier fecha.

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

Agrupar por día local en distintas zonas

Un problema realista es obtener los "usuarios activos diarios en la hora local de cada usuario". Si trunca directamente la marca de tiempo UTC, los límites de medianoche serán incorrectos para los usuarios que no estén en UTC.

Convierta la hora a la zona del usuario antes de truncarla al día. La conversión desplaza la hora local del reloj para que los límites del día coincidan con los de la hora local.

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

Comparar marcas de tiempo de forma segura

Al filtrar una columna timestamptz, compárela con un instante explícito, idealmente un literal UTC o un timestamptz con desplazamiento. Compararla con una cadena sin zona puede hacer que se interprete según la zona horaria de la sesión, que puede ser impredecible.

Así, la comparación no será ambigua independientemente de quién ejecute la consulta.

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

Marcas de tiempo Epoch y Unix

Muchos sistemas almacenan la hora como una época Unix (segundos desde 1970-01-01 UTC). En una entrevista, pueden proporcionarle una columna entera y pedirle que la interprete.

  • Postgres: TO_TIMESTAMP(epoch_seconds) devuelve un timestamptz.
  • Para volver a la época: EXTRACT(EPOCH FROM occurred_at).
  • MySQL: FROM_UNIXTIME() y UNIX_TIMESTAMP().

Los valores de época son intrínsecamente UTC, lo que explica en parte por qué son tan populares para el almacenamiento.

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

Notas sobre zonas horarias entre dialectos

Un resumen rápido para que demuestre soltura en cualquier entorno:

  • Postgres: timestamptz y AT TIME ZONE, con la compatibilidad más completa.
  • MySQL: TIMESTAMP realiza conversiones automáticas mediante time_zone de la sesión; CONVERT_TZ(t, from, to) realiza conversiones explícitas. DATETIME no tiene conocimiento de zonas horarias.
  • SQL Server: datetimeoffset almacena un desplazamiento; AT TIME ZONE 'name' realiza conversiones usando los nombres de zona de Windows.
-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

Ejemplo más detallado: sesiones que atraviesan la medianoche

Una pregunta sutil de generación de informes es cómo contar las sesiones por día natural local cuando una sesión puede atravesar la medianoche. La solución requiere la misma disciplina: convertir a la hora local y después agrupar en intervalos.

Almacene el inicio y el fin como timestamptz; para los informes, obtenga el día local a partir del inicio convertido. Si una sesión debe dividirse entre dos días, tendría que combinarla con una tabla de días, un excelente punto para mencionar como pregunta de seguimiento.

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

Comprobación rápida

Confirme la estrategia de almacenamiento recomendada y explique por qué.

Resumen: zonas horarias y marcas de tiempo

La disciplina que debe conservar es la siguiente:

  • Almacene la hora UTC como timestamptz; conviértala a una zona con nombre únicamente para mostrarla.
  • timestamptz almacena un instante UTC, no una zona horaria, a pesar de su nombre.
  • AT TIME ZONE funciona en ambas direcciones según el tipo de entrada: convierte un timestamptz a la hora local del reloj o interpreta un timestamp simple como perteneciente a una zona.
  • Use nombres de regiones ('America/New_York') para que el horario de verano se aplique automáticamente; evite los desplazamientos fijos.
  • Convierta a la hora local antes de truncar al día y compare las columnas con instantes UTC explícitos.

Preguntas frecuentes

¿La lección «Zonas horarias y marcas de tiempo» es gratis?

Sí — el texto completo de «Zonas horarias y marcas de tiempo» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Interview Prep, actualiza a CoddyKit PRO. El curso de SQL Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «Zonas horarias y marcas de tiempo»?

Almacenar en UTC, convertir zonas y conocer las preguntas habituales de las entrevistas sobre marcas de tiempo. Practicas SQL Interview Prep con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Interview Prep?

No se requiere experiencia previa. SQL Interview Prep en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Zonas horarias y marcas de tiempo»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Interview Prep?

Sí. Cada lección de SQL Interview Prep incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Aritmética de fechas e intervalos
  2. Truncar y agrupar fechas por intervalos
  3. Analizar y dar formato a cadenas
  4. Zonas horarias y marcas de tiempo
← Volver a SQL Interview Prep