Trabajando con tiempo Unix: EPOCH

El tiempo Unix es simplemente una forma de guardar el tiempo como un número de segundos (y a veces milisegundos) que han pasado desde la medianoche del 1 de enero de 1970 en UTC. Este formato es súper popular en desarrollo: es fácil de guardar, transferir y comparar, porque al final es solo un número.

Si lo simplificamos, el tiempo Unix es como un temporizador que empezó a contar en 1970 y nunca se detiene. ¿Ves un número raro como 1697222400? No te asustes — solo significa cuántos segundos han pasado desde el inicio. ¿Quieres saber qué fecha es? ¡Pronto aprenderemos cómo calcularlo!

Este formato se usa mucho para sincronizar datos entre sistemas, guardar marcas de tiempo y cuando necesitas comparar rápido qué pasó antes o después.

Conversión a tiempo Unix

En PostgreSQL hay una función especial EXTRACT(EPOCH FROM ...) que te permite convertir una fecha o timestamp a tiempo Unix.

Vamos a convertir la fecha actual a este formato:

SELECT EXTRACT(EPOCH FROM NOW());

El resultado será algo así como:

1697222400

Este resultado es simplemente la fecha y hora actual en segundos desde 1970.

Así puedes convertir una fecha fija:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00');

Resultado:

1696152000

Ahora sabemos que el 1 de octubre de 2023 a las 12:00 UTC corresponde a este valor.

Conversión desde tiempo Unix

Obvio, también hay una función para convertir de vuelta. Si tienes un tiempo Unix y quieres pasarlo a un formato legible para humanos, la función TO_TIMESTAMP() te salva.

Vamos a convertir un tiempo Unix a fecha y hora

SELECT TO_TIMESTAMP(1697222400);

Resultado:

2023-10-13 00:00:00+00

Ahora vemos que es la fecha 13 de octubre de 2023 a las 00:00 UTC.

Uso práctico del tiempo Unix

Supón que tienes una tabla events donde guardamos info sobre eventos, incluyendo sus marcas de tiempo en formato Unix.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- para guardar tiempo Unix
);

Puedes insertar datos en esta tabla usando marcas de tiempo en formato Unix:

INSERT INTO events (event_name, event_time)
VALUES
('Lanzamiento del servidor', 1697222400),
('Actualización de la base de datos', 1697308800);

Para convertir esta info a un formato legible, solo usa TO_TIMESTAMP:

SELECT event_name, TO_TIMESTAMP(event_time) AS readable_time
FROM events;

Resultado:

Consejos y errores típicos

1. Elegir mal la precisión

Si tu formato de tiempo Unix tiene milisegundos (por ejemplo, 1697222400000), no puedes simplemente pasarlo directo a TO_TIMESTAMP o usarlo en EXTRACT. En estos casos, tienes que dividir el valor por 1000:

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Resultado:

2023-10-13 00:00:00+00

2. Ignorar las zonas horarias

El tiempo Unix está en UTC, así que los resultados siempre serán sin tener en cuenta la zona horaria, a menos que tú mismo conviertas la hora. Por ejemplo:

SELECT TO_TIMESTAMP(1697222400) AT TIME ZONE 'Europe/Moscow';

Resultado:

2023-10-13 03:00:00

3. Problemas con números demasiado grandes

A veces los devs pasan por error un valor de tiempo Unix muy grande (por ejemplo, milisegundos en vez de segundos). Esto da resultados incorrectos:

SELECT TO_TIMESTAMP(1697222400000); -- ¡Habrá error!

Para arreglarlo, divide el número por 1000 correctamente.

Ejemplo: calcular el tiempo de ejecución de una tarea

Vamos con un ejemplo real: tenemos una tabla de tareas tasks y queremos calcular cuánto tiempo ha pasado desde que se creó cada tarea.

Creamos la tabla:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Agregamos algunas tareas:

INSERT INTO tasks (task_name)
VALUES
('Tarea 1'),
('Tarea 2'),
('Tarea 3');

Ahora podemos añadir una columna con el tiempo Unix:

SELECT id, task_name, EXTRACT(EPOCH FROM created_at) AS created_epoch
FROM tasks;

Resultado:

Y calculamos cuánto tiempo ha pasado desde la creación en segundos:

SELECT id, task_name, EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM created_at) AS elapsed_seconds
FROM tasks;

Resultado:

¿Cuándo y para qué usar tiempo Unix?

El tiempo Unix es perfecto para guardar datos temporales cuando transfieres valores entre sistemas, comparas marcas de tiempo y otras tareas donde necesitas poca carga de CPU. Se usa mucho en APIs, desarrollo web, analítica y para sincronizar apps de servidor y cliente. Pero ojo con sus detalles: siempre es UTC, puede estar en segundos o milisegundos, y el formato de almacenamiento puede variar.

Si tienes que integrarte con sistemas externos que "hablan en tiempo Unix", ahora ya sabes cómo manejarlo en PostgreSQL. Usa sin miedo TO_TIMESTAMP y EXTRACT(EPOCH) para convertir de un lado a otro, ¡y todos tus problemas de tiempo estarán resueltos!