DATE_BIN llego en PostgreSQL 14 y resuelve lo que DATE_TRUNC no puede: ajusta una marca de tiempo al inicio de un cubo de cualquier ancho, 15 minutos, 6 horas, 10 dias, medido desde un origen elegido. Es la herramienta adecuada para metricas con un paso no estandar.
Sintaxis e intuicion
La firma es sencilla: DATE_BIN(stride, source, origin). El primer argumento es un interval (el ancho del cubo), el segundo es la marca de tiempo que quieres agrupar, y el tercero es el origin, el punto desde el que se cuentan los intervalos.
SELECT DATE_BIN(
INTERVAL '15 minutes',
TIMESTAMP '2024-01-01 14:37:09',
TIMESTAMP '2024-01-01'
);
El modelo mental: imagina una regla infinita de cubos de ancho stride que empieza en origin. DATE_BIN encuentra el cubo en el que cae source y devuelve su borde izquierdo.
- Siempre trunca hacia abajo, igual que
DATE_TRUNC.
- El resultado se ajusta a la rejilla de
origin, no al inicio de una hora o un dia.
stride debe ser un numero entero de unidades de tiempo fijas (segundos, minutos, horas, dias, semanas).
Donde DATE_TRUNC se queda corto
DATE_TRUNC solo conoce un conjunto fijo de campos: hora, dia, mes. No puede construir un cubo de 15 minutos ni de 6 horas. Con DATE_BIN es una sola linea:
SELECT
DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;
Contar actividad en ventanas de 6 horas es igual de facil, por ejemplo los registros:
SELECT
DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01') AS shift,
COUNT(*) AS signups
FROM users
GROUP BY shift
ORDER BY shift;
Cambia solo el stride y obtienes cualquier paso, sin subconsultas ni aritmetica con EXTRACT.
Por que importa el origin
El origin fija la fase de la rejilla. Para 15 minutos o una hora casi nunca importa, pero en cuanto stride no divide el dia de forma exacta, el origen se vuelve critico.
SELECT DATE_BIN(
INTERVAL '7 days',
TIMESTAMP '2024-03-20 10:00:00',
TIMESTAMP '2024-01-01'
);
Aqui las semanas se cortan desde el 1 de enero, no desde el lunes ISO. Desplaza el origin al dia de la semana que quieras y obtendras "semanas" que empiezan exactamente cuando el negocio lo necesita.
- Usa el mismo
origin en todas las consultas para que los cubos coincidan entre informes.
- Para un dia desplazado (una jornada que empieza a las 9 de la manana) usa un
origin como TIMESTAMP '2024-01-01 09:00:00'.
Trampas: tipos y zonas horarias
La trampa principal es la incompatibilidad de tipos. Si source es timestamptz, entonces origin tambien debe ser timestamptz, o saltara un error. Ademas, para timestamptz los bins se calculan en UTC, asi que tus cubos de "6 horas" se desplazan respecto a la hora local.
SELECT
DATE_BIN(
INTERVAL '6 hours',
created_at AT TIME ZONE 'Europe/Madrid',
TIMESTAMP '2024-01-01'
) AS shift_es,
COUNT(*)
FROM orders
GROUP BY shift_es;
Un par de rastrillos mas que pisar:
- Los meses y los anos estan prohibidos:
INTERVAL '1 month' lanza un error porque su duracion no es constante. Usa DATE_TRUNC para los meses.
DATE_BIN solo existe desde PostgreSQL 14 en adelante, no esta en la 13 ni en versiones anteriores.
Alternativas: TimescaleDB y ClickHouse
DATE_BIN es solido, pero el ecosistema ofrece opciones mas flexibles.
- TimescaleDB trae
time_bucket, el antepasado de DATE_BIN. Tiene una semantica compatible pero funciones mas ricas: intervalos de un mes, un argumento offset y zonas horarias pasadas directamente.
SELECT
time_bucket(INTERVAL '15 minutes', created_at) AS bucket,
SUM(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;
- ClickHouse resuelve el mismo problema con
toStartOfInterval:
SELECT
toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket,
sum(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;
La conclusion: recurre a DATE_TRUNC para unidades estandar, a DATE_BIN para un paso arbitrario en PostgreSQL puro, y a time_bucket cuando necesites meses, desplazamientos y zonas de fabrica.
DATE_BINllego en PostgreSQL 14 y resuelve lo queDATE_TRUNCno puede: ajusta una marca de tiempo al inicio de un cubo de cualquier ancho, 15 minutos, 6 horas, 10 dias, medido desde un origen elegido. Es la herramienta adecuada para metricas con un paso no estandar.Sintaxis e intuicion
La firma es sencilla:
DATE_BIN(stride, source, origin). El primer argumento es uninterval(el ancho del cubo), el segundo es la marca de tiempo que quieres agrupar, y el tercero es elorigin, el punto desde el que se cuentan los intervalos.SELECT DATE_BIN( INTERVAL '15 minutes', TIMESTAMP '2024-01-01 14:37:09', TIMESTAMP '2024-01-01' ); -- 2024-01-01 14:30:00El modelo mental: imagina una regla infinita de cubos de ancho
strideque empieza enorigin.DATE_BINencuentra el cubo en el que caesourcey devuelve su borde izquierdo.DATE_TRUNC.origin, no al inicio de una hora o un dia.stridedebe ser un numero entero de unidades de tiempo fijas (segundos, minutos, horas, dias, semanas).Donde DATE_TRUNC se queda corto
DATE_TRUNCsolo conoce un conjunto fijo de campos: hora, dia, mes. No puede construir un cubo de 15 minutos ni de 6 horas. ConDATE_BINes una sola linea:SELECT DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket, COUNT(*) AS orders, SUM(amount) AS revenue FROM orders WHERE status = 'paid' GROUP BY bucket ORDER BY bucket;Contar actividad en ventanas de 6 horas es igual de facil, por ejemplo los registros:
SELECT DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01') AS shift, COUNT(*) AS signups FROM users GROUP BY shift ORDER BY shift;Cambia solo el
stridey obtienes cualquier paso, sin subconsultas ni aritmetica conEXTRACT.Por que importa el origin
El
originfija la fase de la rejilla. Para 15 minutos o una hora casi nunca importa, pero en cuantostrideno divide el dia de forma exacta, el origen se vuelve critico.SELECT DATE_BIN( INTERVAL '7 days', TIMESTAMP '2024-03-20 10:00:00', TIMESTAMP '2024-01-01' ); -- 2024-03-18 00:00:00Aqui las semanas se cortan desde el 1 de enero, no desde el lunes ISO. Desplaza el
original dia de la semana que quieras y obtendras "semanas" que empiezan exactamente cuando el negocio lo necesita.originen todas las consultas para que los cubos coincidan entre informes.origincomoTIMESTAMP '2024-01-01 09:00:00'.Trampas: tipos y zonas horarias
La trampa principal es la incompatibilidad de tipos. Si
sourceestimestamptz, entoncesorigintambien debe sertimestamptz, o saltara un error. Ademas, paratimestamptzlos bins se calculan en UTC, asi que tus cubos de "6 horas" se desplazan respecto a la hora local.SELECT DATE_BIN( INTERVAL '6 hours', created_at AT TIME ZONE 'Europe/Madrid', TIMESTAMP '2024-01-01' ) AS shift_es, COUNT(*) FROM orders GROUP BY shift_es;Un par de rastrillos mas que pisar:
INTERVAL '1 month'lanza un error porque su duracion no es constante. UsaDATE_TRUNCpara los meses.DATE_BINsolo existe desde PostgreSQL 14 en adelante, no esta en la 13 ni en versiones anteriores.Alternativas: TimescaleDB y ClickHouse
DATE_BINes solido, pero el ecosistema ofrece opciones mas flexibles.time_bucket, el antepasado deDATE_BIN. Tiene una semantica compatible pero funciones mas ricas: intervalos de un mes, un argumentooffsety zonas horarias pasadas directamente.SELECT time_bucket(INTERVAL '15 minutes', created_at) AS bucket, SUM(amount) AS revenue FROM orders GROUP BY bucket ORDER BY bucket;toStartOfInterval:SELECT toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket, sum(amount) AS revenue FROM orders GROUP BY bucket ORDER BY bucket;La conclusion: recurre a
DATE_TRUNCpara unidades estandar, aDATE_BINpara un paso arbitrario en PostgreSQL puro, y atime_bucketcuando necesites meses, desplazamientos y zonas de fabrica.