DATE_TRUNC en SQL: redondear fechas a periodos para series temporales

DATE_TRUNC descarta cualquier parte de una marca de tiempo mas fina que el periodo que pidas y devuelve el inicio de ese periodo. Es el caballo de batalla de toda analitica temporal: filas con precision de milisegundos se agrupan en cubos limpios por hora, dia, semana, mes o ano.

Que hace exactamente DATE_TRUNC

El primer argumento es el campo ('hour', 'day', 'week', 'month', 'year', etc.); el segundo es un timestamp o una date. El resultado se redondea hacia abajo al inicio del periodo, poniendo a cero las unidades menores.

SELECT
  DATE_TRUNC('month', TIMESTAMP '2026-06-17 14:32:09') AS month_start,
  DATE_TRUNC('day',   TIMESTAMP '2026-06-17 14:32:09') AS day_start,
  DATE_TRUNC('hour',  TIMESTAMP '2026-06-17 14:32:09') AS hour_start;
-- 2026-06-01 00:00:00 | 2026-06-17 00:00:00 | 2026-06-17 14:00:00

Propiedades clave:

• Siempre trunca hacia abajo, nunca al mas cercano: 14:32 con 'hour' da 14:00.
• Devuelve el mismo tipo timestamp, asi que es facil comparar y agrupar.
• 'week' en PostgreSQL empieza el lunes (ISO), una sorpresa habitual si esperabas domingo.

Agrupar una serie temporal en cubos

El caso estrella es calcular una metrica por periodo. Cuantos usuarios se registraron cada mes?

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*)                        AS signups
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

La misma idea para ingresos diarios, ahora desde orders y filtrando por estado:

SELECT
  DATE_TRUNC('day', created_at) AS day,
  SUM(amount)                   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

Agrupa por la expresion DATE_TRUNC(...), no por el created_at crudo, o cada segundo distinto se convertira en una fila propia.

Rellenar huecos con generate_series

GROUP BY solo devuelve periodos que tienen datos. Si un dia no hubo pedidos, la fila simplemente desaparece y tu grafico muestra una linea rota. Para tener un eje continuo, genera un calendario y haz un LEFT JOIN contra el.

SELECT
  cal.day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
       DATE_TRUNC('day', DATE '2026-06-01'),
       DATE_TRUNC('day', DATE '2026-06-30'),
       INTERVAL '1 day'
     ) AS cal(day)
LEFT JOIN orders o
  ON DATE_TRUNC('day', o.created_at) = cal.day
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Ahora aparecen los 30 dias, y los dias sin pedidos informan honestamente un 0.

Zonas horarias: el gran tropiezo

El DATE_TRUNC(campo, ts) de dos argumentos no calcula en UTC. Cuando ts es un timestamptz, PostgreSQL trunca segun el ajuste TimeZone de la sesion, asi que la misma consulta puede colocar un pedido en dias distintos del calendario segun quien la ejecute. Un pedido a las 2026-06-01 01:30 UTC ya pertenece al 1 de junio para una sesion en Madrid, pero para una en UTC-5 sigue siendo 31 de mayo. Eso desplaza tus cubos mensuales y diarios justo en el limite del dia.

Por eso conviene fijar siempre la zona en vez de confiar en el ajuste de la sesion. Convierte con AT TIME ZONE primero y luego trunca:

SELECT
  DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Madrid') AS day_es,
  COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Madrid');

PostgreSQL moderno (14+) ofrece ademas una forma de tres argumentos, DATE_TRUNC(campo, ts, zona), que trunca un timestamptz en la zona que indiques y elimina cualquier duda sobre el ajuste de la sesion:

SELECT
  DATE_TRUNC('day', created_at, 'Europe/Madrid') AS day_es,
  COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at, 'Europe/Madrid');

Para un paso arbitrario (por ejemplo 15 minutos) usa date_bin('15 minutes', created_at, TIMESTAMP '2026-01-01').

Equivalentes en MySQL y ClickHouse

DATE_TRUNC no es universal y la sintaxis varia.

• MySQL no tiene DATE_TRUNC. Trunca a mes con DATE_FORMAT y a dia con DATE().

SELECT
  DATE_FORMAT(created_at, '%Y-%m-01') AS month,
  COUNT(*)                            AS signups
FROM users
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01');

• ClickHouse trae funciones dedicadas: toStartOfMonth, toStartOfDay, toStartOfHour, y toStartOfInterval para un paso arbitrario.

SELECT
  toStartOfMonth(created_at) AS month,
  count()                    AS signups
FROM users
GROUP BY month
ORDER BY month;

Recuerda una cosa: la intencion es identica en todas partes, poner a cero la cola de la marca de tiempo para que las filas encajen en cubos. Solo cambia el nombre de la funcion.