SQL · Guías · 30 de octubre de 2026 · 11 min de lectura

Funciones de fecha en SQL: extraer, truncar, sumar intervalos y diferencia entre fechas

Cuatro tipos de funciones de fecha cubren casi cualquier tarea de un analista. EXTRACT saca una parte de una fecha (el año, el mes, la hora, el día de la semana). date_trunc redondea una marca de tiempo hacia abajo al inicio de su día, semana o mes, que es como se agrupa por periodo. Los intervalos (+ INTERVAL '2 hours') mueven una fecha hacia delante o hacia atrás. La resta te da el tiempo entre dos fechas. Domina estas cuatro y podrás montar la mayoría de informes basados en el tiempo.

Todos los ejemplos usan la base de datos de una aerolínea del curso de SQL de Nomto: 33.121 vuelos del 12 de julio al 10 de septiembre de 2026 y 262.788 reservas, con horas en hora de Moscú (valores en inglés, dinero en dólares). Las consultas usan sintaxis de PostgreSQL y se ejecutan en DuckDB, el motor de la sala de SQL de Nomto; cada tabla de resultados es la salida real. Cuando PostgreSQL y DuckDB llaman distinto a una función, la guía lo indica.

DATE, TIMESTAMP e INTERVAL

Tres tipos hacen el trabajo:

En los datos de la aerolínea, flights.scheduled_departure, flights.actual_departure y bookings.book_date son marcas de tiempo (TIMESTAMP). Para convertir una marca de tiempo en fecha, haz un cast: scheduled_departure::date (o el estándar CAST(scheduled_departure AS DATE)). Las dos formas funcionan en PostgreSQL y DuckDB.

Vuelos por día en los tres primeros días de agosto:

SELECT scheduled_departure::date AS day,
       COUNT(*) AS flights
FROM flights
WHERE scheduled_departure >= '2026-08-01'
  AND scheduled_departure <  '2026-08-04'
GROUP BY 1
ORDER BY 1;

Fíjate en el filtro: «desde el inicio del primer día, y antes del inicio del día siguiente al último». Funciona con marcas de tiempo sin aplicar ninguna función a la columna, lo que además permite a la base de datos usar un índice. BETWEEN '2026-08-01' AND '2026-08-03' se pararía a medianoche del 3 de agosto y perdería casi un día entero sin avisar.

EXTRACT: sacar el año, el mes, la hora o el día de la semana

EXTRACT(parte FROM valor) devuelve un componente como número:

SELECT flight_no,
       scheduled_departure,
       EXTRACT(year  FROM scheduled_departure) AS yr,
       EXTRACT(month FROM scheduled_departure) AS mon,
       EXTRACT(day   FROM scheduled_departure) AS d,
       EXTRACT(hour  FROM scheduled_departure) AS hr,
       EXTRACT(dow   FROM scheduled_departure) AS dow
FROM flights
WHERE flight_id = 1;

El 12 de julio de 2026 es domingo, y dow devuelve 0: tanto en PostgreSQL como en DuckDB, dow va de 0 (domingo) a 6 (sábado). Si quieres lunes = 1 y domingo = 7, usa isodow. Otras partes útiles: quarter (trimestre), week (número de semana ISO), minute y epoch (segundos desde 1970, o la duración de un intervalo en segundos; más sobre esto abajo).

El uso típico es agrupar por una parte de la fecha. Las horas de salida con más vuelos:

SELECT EXTRACT(hour FROM scheduled_departure) AS hour,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY flights DESC
LIMIT 5;

Y por día de la semana, empezando por el lunes:

SELECT EXTRACT(isodow FROM scheduled_departure) AS iso_dow,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1;

En esta programación, el viernes es el día más tranquilo y el domingo, el de más vuelos. Ten en cuenta que el periodo no es un número entero de semanas, así que algunos días de la semana aparecen una vez más que otros. Para una comparación justa, divide por el número de veces que aparece cada día en el rango.

PostgreSQL frente a DuckDB. Los dos admiten EXTRACT(parte FROM x) y la forma de función date_part('parte', x). PostgreSQL 14+ devuelve numeric en EXTRACT y double precision en date_part; DuckDB devuelve enteros. DuckDB tiene además atajos como year(x), hour(x), dayname(x) y monthname(x) que PostgreSQL no tiene.

date_trunc: agrupar por día, semana o mes

EXTRACT(month ...) devuelve 7 para julio de cualquier año, lo que está mal cuando los datos abarcan más de un año. Para agrupar por periodo del calendario, redondea la marca de tiempo hacia abajo al inicio del periodo con date_trunc('unidad', valor):

SELECT date_trunc('month', book_date) AS month,
       COUNT(*) AS bookings,
       ROUND(SUM(total_amount)) AS revenue
FROM bookings
GROUP BY 1
ORDER BY 1;

Aquí junio y agosto son meses incompletos: las reservas empiezan el 17 de junio y los datos se cortan el 11 de agosto. Comprueba siempre la primera y la última fecha antes de comparar periodos.

Las semanas funcionan igual, y date_trunc('week', ...) empieza la semana en lunes tanto en PostgreSQL como en DuckDB:

SELECT date_trunc('week', scheduled_departure) AS week,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1
LIMIT 5;

La primera semana solo contiene el domingo 12 de julio, y por eso tiene 558 vuelos. Otras unidades: 'day', 'hour', 'quarter', 'year'.

date_trunc sobre una marca de tiempo devuelve una marca de tiempo. Añade ::date si quieres una fecha limpia en el informe. Ojo: un periodo sin filas simplemente no aparece; si necesitas todos los días o meses en el resultado, también los vacíos, construye antes un calendario, como se explica en la guía del calendario de fechas, en inglés.

Sumar y restar intervalos

Suma o resta un INTERVAL para mover una marca de tiempo:

SELECT flight_no,
       scheduled_departure,
       scheduled_departure + INTERVAL '2 hours' AS plus_2h,
       scheduled_departure - INTERVAL '1 day'   AS minus_1d,
       CAST(scheduled_departure AS DATE) + 7    AS week_later
FROM flights
WHERE flight_id = 1;

Sumar un entero a un DATE suma días, en las dos bases de datos. Con una marca de tiempo, usa un intervalo.

La aritmética de meses requiere cuidado, porque los meses tienen longitudes distintas. Sumar un mes al 31 de enero no se desborda hasta marzo: se queda en el último día de febrero:

SELECT DATE '2026-01-31' + INTERVAL '1 month' AS a;

La misma idea te da el último día de un mes: inicio del mes, más un mes, menos un día.

SELECT d,
       date_trunc('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS month_end,
       last_day(d) AS ld
FROM (SELECT DATE '2026-02-10' AS d);

last_day existe en DuckDB (y en MySQL), pero no en PostgreSQL; la versión con date_trunc funciona en todas partes.

Los intervalos son también la forma de escribir filtros de «los últimos N días». En este conjunto de datos la instantánea se tomó el 11 de agosto de 2026 a las 18:00, así que usa ese momento en lugar de now(): WHERE book_date >= TIMESTAMP '2026-08-11 18:00' - INTERVAL '7 days'. En una base de datos en vivo, now() y current_date funcionan tanto en PostgreSQL como en DuckDB.

Diferencia entre dos fechas en días y minutos

Restar una marca de tiempo de otra da un intervalo. Los mayores retrasos de salida:

SELECT flight_no,
       scheduled_departure,
       actual_departure,
       actual_departure - scheduled_departure AS delay,
       EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60 AS delay_min
FROM flights
WHERE actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 3;

Un intervalo como 04:37:00 se lee bien, pero no puedes promediarlo en un KPI ni compararlo con un número. EXTRACT(epoch FROM intervalo) lo convierte en segundos; divide entre 60 para minutos o entre 3.600 para horas. Funciona igual en PostgreSQL y DuckDB, así que es el hábito más seguro. Retraso medio por mes:

SELECT date_trunc('month', scheduled_departure)::date AS month,
       COUNT(*) AS departed,
       ROUND(AVG(EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights
WHERE actual_departure IS NOT NULL
GROUP BY 1
ORDER BY 1;

Para días completos, resta dos fechas. DATE - DATE devuelve un número entero de días en las dos bases de datos. ¿Con cuánta antelación reservan los pasajeros? Cada fila de abajo es un tramo de vuelo de una reserva:

SELECT b.book_ref,
       b.book_date,
       f.scheduled_departure,
       f.scheduled_departure::date - b.book_date::date AS days_ahead
FROM bookings b
JOIN tickets t         USING (book_ref)
JOIN ticket_flights tf USING (ticket_no)
JOIN flights f         USING (flight_id)
ORDER BY b.book_ref, f.scheduled_departure
LIMIT 5;

Agrupa la diferencia en tramos con CASE y ya tienes un informe de ventana de reserva:

SELECT CASE WHEN days < 7  THEN '0-6 days'
            WHEN days < 14 THEN '7-13 days'
            WHEN days < 30 THEN '14-29 days'
            ELSE '30+ days' END AS booked_ahead,
       COUNT(*) AS segments
FROM (
  SELECT f.scheduled_departure::date - b.book_date::date AS days
  FROM bookings b
  JOIN tickets t         USING (book_ref)
  JOIN ticket_flights tf USING (ticket_no)
  JOIN flights f         USING (flight_id)
) s
GROUP BY 1
ORDER BY MIN(days);

Si quieres repasar cómo funcionan estos tramos, lo tienes en la guía de CASE WHEN en SQL.

date_diff, DATEDIFF y age

Cada base de datos ofrece atajos distintos, y aquí es donde se rompen las consultas copiadas:

PostgreSQL no tiene date_diff ni DATEDIFF, y DuckDB no tiene to_char. age devuelve un intervalo que respeta el calendario: age(TIMESTAMP '2026-08-11 18:00', TIMESTAMP '2026-06-17 00:00') da 1 month 24 days 18:00:00 en DuckDB (PostgreSQL muestra 1 mon 24 days 18:00:00).

Una trampa del date_diff de DuckDB: cuenta cuántas fronteras se cruzan, no cuántas unidades completas han pasado:

SELECT date_diff('day', TIMESTAMP '2026-07-12 23:50', TIMESTAMP '2026-07-13 00:10') AS dd;

Veinte minutos, un «día». Para días del calendario (reservó el 10, voló el 24) es justo lo que quieres. Para tiempo transcurrido (¿el retraso superó un día?), calcula el intervalo y usa EXTRACT(epoch ...).

Errores frecuentes

• Agrupar por EXTRACT(month ...) con varios años. Julio de 2025 y julio de 2026 acaban en el mismo grupo. Usa date_trunc('month', ...).
• BETWEEN con marcas de tiempo. El límite superior es la medianoche del inicio del último día. Usa >= inicio AND < inicio_siguiente.
• Envolver la columna en una función dentro del WHERE. WHERE date_trunc('day', scheduled_departure) = '2026-08-01' es correcto, pero obliga a la base de datos a calcular la función en cada fila. Un rango sobre la columna original es equivalente y puede usar un índice.
• Promediar intervalos para un KPI. Conviértelos antes a minutos u horas con EXTRACT(epoch ...), para que el número sea fácil de leer y comparar.
• Usar now() sobre una instantánea. Los conjuntos de datos de práctica e históricos tienen su propio «hoy». Escribe a mano el momento de la instantánea.
• Olvidarte de las zonas horarias. Todas las horas de este conjunto de datos están en hora de Moscú, guardadas como marcas de tiempo simples. En datos reales, comprueba si una columna es timestamp o timestamptz y en qué zona se registraron los eventos antes de extraer la hora.

Las fechas suelen ir de la mano de la agrupación y los totales acumulados; mira GROUP BY y HAVING y la guía de totales acumulados, en inglés. Las columnas de texto tienen sus propias herramientas, explicadas en las funciones de texto en SQL.

Preguntas frecuentes

¿Cómo saco solo la fecha de una marca de tiempo en SQL?

Con un cast: scheduled_departure::date o CAST(scheduled_departure AS DATE). Las dos formas funcionan en PostgreSQL y DuckDB. date_trunc('day', ...) también quita la hora, pero mantiene el tipo marca de tiempo.

¿Qué diferencia hay entre EXTRACT y date_trunc?

EXTRACT devuelve una parte de la fecha como número (7 para julio). date_trunc devuelve una marca de tiempo completa redondeada hacia abajo al inicio del periodo (2026-07-01 00:00:00). Usa EXTRACT para preguntas de estacionalidad («¿qué hora tiene más vuelos?») y date_trunc para series temporales («reservas por mes»).

¿Cómo calculo los días entre dos fechas en PostgreSQL?

Resta dos fechas: end_date - start_date devuelve un entero. Si las columnas son marcas de tiempo, conviértelas antes (t2::date - t1::date) para días del calendario, o usa EXTRACT(epoch FROM t2 - t1) / 86400 para días transcurridos con decimales.

¿date_trunc(‘week’) empieza la semana en lunes o en domingo?

En lunes, en PostgreSQL y DuckDB. Para semanas que empiezan en domingo, desplaza la fecha: date_trunc('week', d + INTERVAL '1 day') - INTERVAL '1 day'.

¿Existe DATEADD en PostgreSQL?

No. Suma un intervalo: order_date + INTERVAL '3 days', o + 3 sobre una columna DATE.

También en English, Português (Brasil), Tiếng Việt, 简体中文