UNIX_TIMESTAMP

La función UNIX_TIMESTAMP convierte una fecha en el número de segundos transcurridos desde el 1 de enero de 1970 a las 00:00:00 UTC, lo que se conoce como "timestamp Unix" o "epoch time". Es la operación inversa de FROM_UNIXTIME: mientras aquella convierte un número en una fecha legible, UNIX_TIMESTAMP convierte una fecha en un número entero.

Este formato numérico es el estándar universal para representar fechas en programación. Cuando necesitas enviar fechas a una API REST, almacenar marcas de tiempo portables entre sistemas, o hacer aritmética de tiempo en segundos, UNIX_TIMESTAMP es la función que transforma tus fechas de MySQL en el formato que el resto del mundo tecnológico entiende.

Sintaxis

-- Timestamp actual (momento de ejecución)
UNIX_TIMESTAMP()
 
-- Timestamp de una fecha específica
UNIX_TIMESTAMP(fecha)

Sin argumentos, devuelve el timestamp Unix del momento actual (equivalente a UNIX_TIMESTAMP(NOW())). Con un argumento de fecha o datetime, devuelve el timestamp correspondiente a esa fecha. El resultado es un entero (o decimal si la fecha incluye fracciones de segundo).

Comportamiento básico

Veamos cómo UNIX_TIMESTAMP convierte fechas a números:

SELECT
    UNIX_TIMESTAMP('2025-01-01 00:00:00') AS inicio_2025,
    UNIX_TIMESTAMP('2025-06-15 12:30:00') AS mediados_2025,
    UNIX_TIMESTAMP('1970-01-01 00:00:00') AS epoca,
    UNIX_TIMESTAMP('2000-01-01 00:00:00') AS milenio;

El 1 de enero de 1970 devuelve 0 porque es el punto de referencia de la época Unix. Cada segundo que pasa desde ese momento suma 1 al contador. El 1 de enero del 2000 fue el segundo número 946.684.800, y el inicio de 2025 es el segundo número 1.735.689.600: casi 1.7 mil millones de segundos de historia computacional.

Sin argumentos: el momento actual

Cuando llamas a UNIX_TIMESTAMP() sin argumentos, obtienes el timestamp del instante en que se ejecuta la consulta:

SELECT
    NOW() AS ahora,
    UNIX_TIMESTAMP() AS timestamp_ahora,
    UNIX_TIMESTAMP(NOW()) AS timestamp_now;

UNIX_TIMESTAMP() y UNIX_TIMESTAMP(NOW()) producen el mismo resultado. La forma sin argumentos es simplemente más concisa.

Caso práctico: preparar datos para APIs

Cuando tu aplicación envía datos a una API externa que espera fechas en formato timestamp Unix, puedes hacer la conversión directamente en la consulta:

SELECT
    p.id,
    p.cliente_id,
    p.total,
    UNIX_TIMESTAMP(p.fecha_pedido) AS created_at,
    UNIX_TIMESTAMP(p.fecha_envio) AS shipped_at
FROM (
    SELECT 1 AS id, 101 AS cliente_id, 245.50 AS total,
           '2025-06-15 09:30:00' AS fecha_pedido, '2025-06-16 14:00:00' AS fecha_envio
    UNION SELECT 2, 102, 890.00, '2025-06-15 11:45:00', '2025-06-17 10:30:00'
    UNION SELECT 3, 103, 67.20, '2025-06-16 08:15:00', NULL
) p;

Los campos created_at y shipped_at ahora están en un formato que cualquier API, lenguaje de programación o sistema externo puede interpretar sin ambigüedades de zona horaria o formato de fecha.

Caso práctico: calcular diferencias en segundos

Una ventaja de UNIX_TIMESTAMP sobre DATEDIFF es que trabaja en segundos, no en días. Esto permite calcular diferencias de tiempo con mayor precisión:

SELECT
    nombre,
    fecha_inicio,
    fecha_fin,
    UNIX_TIMESTAMP(fecha_fin) - UNIX_TIMESTAMP(fecha_inicio) AS segundos,
    ROUND((UNIX_TIMESTAMP(fecha_fin) - UNIX_TIMESTAMP(fecha_inicio)) / 3600, 1) AS horas,
    ROUND((UNIX_TIMESTAMP(fecha_fin) - UNIX_TIMESTAMP(fecha_inicio)) / 86400, 1) AS dias
FROM (
    SELECT 'Proyecto Alpha' AS nombre,
           '2025-01-15 08:00:00' AS fecha_inicio, '2025-01-15 17:30:00' AS fecha_fin
    UNION SELECT 'Proyecto Beta', '2025-03-01 09:00:00', '2025-03-15 18:00:00'
    UNION SELECT 'Proyecto Gamma', '2025-06-01 00:00:00', '2025-06-01 02:30:00'
) proyectos;

Dividir entre 3600 convierte segundos a horas, y entre 86400 convierte a días. Esta aritmética simple es posible porque UNIX_TIMESTAMP reduce todo a un contador lineal de segundos.

Caso práctico: almacenamiento portable de fechas

Algunos desarrolladores prefieren almacenar fechas como timestamps Unix (en columnas INT o BIGINT) en lugar de usar tipos DATETIME. Las ventajas son la portabilidad entre bases de datos y la independencia de zonas horarias:

CREATE TABLE sesiones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT,
    inicio INT UNSIGNED,
    fin INT UNSIGNED
);
 
-- Insertar con UNIX_TIMESTAMP
INSERT INTO sesiones (usuario_id, inicio, fin) VALUES
    (1, UNIX_TIMESTAMP('2025-06-18 09:00:00'), UNIX_TIMESTAMP('2025-06-18 17:30:00'));
 
-- Consultar convirtiendo de vuelta
SELECT
    usuario_id,
    FROM_UNIXTIME(inicio) AS hora_inicio,
    FROM_UNIXTIME(fin) AS hora_fin,
    (fin - inicio) / 3600 AS horas_trabajadas
FROM sesiones;

La aritmética es trivial porque los timestamps son simples números enteros. No necesitas funciones especiales para calcular la diferencia: una resta basta.

Límites del rango

UNIX_TIMESTAMP comparte las limitaciones del rango del timestamp Unix. No puede representar fechas anteriores a 1970 ni, en sistemas de 32 bits, posteriores a 2038:

SELECT
    UNIX_TIMESTAMP('1970-01-01 00:00:00') AS minimo,
    UNIX_TIMESTAMP('1969-12-31 23:59:59') AS antes_epoca,
    UNIX_TIMESTAMP('2038-01-19 03:14:07') AS maximo_32bit;

Las fechas anteriores a la época Unix devuelven 0 en MySQL (no valores negativos como en algunos lenguajes de programación). En sistemas MySQL de 64 bits con versiones recientes, el rango se extiende hasta el año 3000 o más.

UNIX_TIMESTAMP con NULL

SELECT
    UNIX_TIMESTAMP(NULL) AS nulo,
    UNIX_TIMESTAMP('fecha_invalida') AS invalido;

Un detalle importante: mientras que NULL produce NULL, una cadena que no es una fecha válida produce 0, no NULL. Esto puede causar errores sutiles si no validas tus datos de entrada. Siempre verifica que el resultado no sea 0 cuando trabajes con datos que podrían contener fechas mal formadas.

Combinación con otras funciones

UNIX_TIMESTAMP se combina con funciones aritméticas para crear marcas de tiempo relativas. Un patrón habitual es generar tokens o enlaces con fecha de expiración:

SELECT
    usuario_id,
    UNIX_TIMESTAMP() AS token_creado,
    UNIX_TIMESTAMP() + 3600 AS expira_1h,
    UNIX_TIMESTAMP() + 86400 AS expira_24h,
    UNIX_TIMESTAMP() + (7 * 86400) AS expira_7d,
    FROM_UNIXTIME(UNIX_TIMESTAMP() + 3600) AS expira_1h_legible
FROM (SELECT 1001 AS usuario_id) u;

Sumar segundos al timestamp actual es la forma más sencilla de calcular fechas de expiración. 3600 segundos es una hora, 86400 es un día y 604800 es una semana. Este patrón es el estándar para tokens de autenticación, enlaces de restablecimiento de contraseña y cachés temporales.

También puedes verificar si un timestamp ha expirado con una simple comparación numérica, que es más eficiente que comparar fechas:

SELECT *
FROM tokens
WHERE expira > UNIX_TIMESTAMP();

En el siguiente artículo veremos STR_TO_DATE para convertir cadenas de texto en fechas.