Cómo Agrupar por Semana en PostgreSQL

¿Necesita analizar datos por semana en PostgreSQL? Aquí le mostramos cómo agrupar sus registros usando funciones de fecha incorporadas para obtener información clara basada en el tiempo.

Agrupar datos por semana es un requerimiento común en los reportes. A menudo se usa en informes de negocios para rastrear métricas como ingresos semanales, registros de usuarios o tráfico de sitios web. La agrupación por semanas ayuda a suavizar las fluctuaciones diarias y ofrece una visión más clara de las tendencias a lo largo del tiempo. Es especialmente útil cuando se compara el rendimiento a través de intervalos de tiempo consistentes, como el crecimiento "Semana sobre Semana" o el análisis de cohortes semanales.

PostgreSQL ofrece herramientas poderosas para trabajar con fechas, pero agrupar por semana-especialmente cuando necesita un día de inicio o zona horaria específicos-puede ser complicado si no está familiarizado con las funciones correctas.

Este artículo le mostrará cómo agrupar por semanas en PostgreSQL usando date_trunc() y date_bin(), cómo ajustar zonas horarias y cómo establecer un día de inicio de semana personalizado como miércoles o jueves.

¿Quiere construir habilidades sólidas y prácticas en PostgreSQL desde cero? Pruebe el cursoSQL de la A a la Z en PostgreSQL en LearnSQL.es. Está diseñado para llevarlo de principiante a usuario seguro de SQL con ejercicios del mundo real y guía paso a paso.

Agrupación por semana con date_trunc()

La función date_trunc() es la forma más común de agrupar datos por semana en PostgreSQL. Trunca una marca de tiempo o fecha al inicio de una unidad de tiempo especificada-como día, semana o mes.

Uso básico:

date_trunc('week', some_date)

Esto devolverá una nueva fecha que representa el inicio de la semana ISO, que es el lunes a las 00:00:00.

Ejemplo:

Supongamos que tiene una columna created_at y desea contar las inscripciones por semana:

SELECT
  date_trunc('week', created_at) AS week_start,
  COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

Esta consulta agrupa a los usuarios por la semana en la que se registraron, comenzando cada semana el lunes.

A continuación se muestra el resultado de esta consulta:

Observe que el resultado de date_trunc('week', some_date) incluye tanto la fecha como la hora, con la hora fijada en 00:00:00 (medianoche).

Agrupación por semanas en una zona horaria específica

Cuando se trabaja con marcas de tiempo, especialmente en aplicaciones globales, es esencial tener en cuenta la zona horaria. La actividad de un usuario puede tener lugar el domingo por la noche en una zona horaria, pero aparecer el lunes por la mañana en UTC. Esto puede afectar a la forma en que los datos se agrupan en semanas, dando lugar a informes incorrectos si se ignoran las zonas horarias.

Por defecto, PostgreSQL agrupa las marcas de tiempo basándose en la zona horaria de la sesión, que normalmente se establece cuando se establece la conexión. Esto significa que date_trunc('week', some_timestamptz_column) utilizará la zona horaria actual de la sesión. Si no está configurando explícitamente la zona horaria, PostgreSQL utilizará la predeterminada de la configuración de su servidor o cliente.

Para garantizar la coherencia, especialmente en los informes, puede utilizar la cláusula AT TIME ZONE para convertir las marcas de tiempo a una zona horaria específica antes de truncarlas.

Ejemplo de uso:

SELECT
  date_trunc('week', created_at AT TIME ZONE 'UTC') AS week_start_utc,
  COUNT(*) AS signups
FROM users
GROUP BY week_start_utc
ORDER BY week_start_utc;

Esta consulta trunca cada marca de fecha y hora de created_at al principio de la semana en UTC, lo que garantiza que las agrupaciones semanales sean coherentes en todas las zonas horarias.

Si está trabajando con una columna timestamptz y desea agrupar por una hora local específica, utilice:

date_trunc('week', created_at AT TIME ZONE 'America/New_York')

Esto alinea sus semanas al comienzo del lunes en la hora local de Nueva York.

Días de inicio de semana personalizados (por ejemplo, miércoles o jueves)

La función date_trunc('week', some_date) en PostgreSQL siempre alinea las semanas para que comiencen en lunes, siguiendo la definición de semana ISO. Esto es fijo y no puede cambiarse directamente a través de date_trunc().

Si su informe requiere que las semanas comiencen en un día diferente, como el miércoles o el jueves, tendrá que aplicar una solución sencilla: desplace la fecha antes del truncamiento y vuelva a desplazarla después.

Lógica de la solución

Para que la semana comience en un día concreto, reste el número de días adecuado antes del truncamiento y vuelva a sumarlo:

-- Week starting on Wednesday
date_trunc('week', some_date - interval '2 days') + interval '2 days'

Esto desplaza el calendario 2 días hacia atrás (de modo que el lunes se convierte en sábado), trunca hasta el inicio de esa semana ajustada (sábado) y vuelve a desplazar hacia delante hasta el miércoles.

Ejemplos de diferentes días de inicio de semana

Utilice este patrón dentro de sus cláusulas SELECT, GROUP BY, o ORDER BY para alinear los datos con la estructura semanal que prefiera.

Agrupación flexible de fecha y hora con date_bin() (PostgreSQL 14+)

PostgreSQL 14 introdujo una nueva y poderosa función: date_bin(). Le da más control que date_trunc() al agrupar por tiempo, especialmente si desea que las semanas comiencen en una fecha específica o en un día de la semana personalizado.

date_bin() le permite agrupar marcas de tiempo en periodos de tiempo iguales -como semanas, días u horas- a partir de cualquier fecha y hora que elija. Este punto de inicio se denomina ancla.

El ancla define cuándo comienza el primer grupo. PostgreSQL entonces construye los otros grupos basados en eso-tanto hacia adelante como hacia atrás en el tiempo. Así, si su ancla es el 1 de enero de 2025, y usted agrupa por 7 días, PostgreSQL creará intervalos de una semana a partir de esa fecha, y también intervalos anteriores que se ajusten al mismo patrón.

Sintaxis

date_bin(interval, timestamp, anchor)

• interval: la longitud de cada grupo (por ejemplo '7 days')
• timestampla columna que está agrupando (por ejemplo, created_at)
• anchorla fecha y la hora exactas a las que debe alinearse la agrupación.

Ejemplo: Agrupación por intervalos de 1 semana a partir del 1 de enero

SELECT
  date_bin('7 days', created_at, TIMESTAMP '2025-01-01') AS custom_week,
  COUNT(*) AS signups
FROM users
GROUP BY custom_week
ORDER BY custom_week;

Agrupa a los usuarios en intervalos de 7 días a partir del 1 de enero de 2025, independientemente del día de la semana que sea. Puede utilizar cualquier fecha de anclaje que desee, por ejemplo, el inicio de su año fiscal o el día de lanzamiento de su producto.

Utilice date_bin() si desea que sus semanas comiencen en un día personalizado o en una fecha exacta, si necesita intervalos de tiempo que no se basen en unidades de calendario (por ejemplo, cada 10 días o 6 horas) o si desea tener un control total sobre cómo se agrupan los datos basados en el tiempo.

Si utiliza PostgreSQL 14 o posterior, date_bin() es una alternativa flexible que puede hacer que sus informes semanales sean más precisos y se ajusten a sus necesidades específicas.

Conclusión

Agrupar por semana en PostgreSQL puede ser simple o altamente personalizable, dependiendo de sus necesidades. Puede usar date_trunc() para semanas ISO estándar (que comienzan el lunes) o aplicar pequeños ajustes para comenzar las semanas en cualquier día que desee. Para aún más flexibilidad -como establecer una fecha de anclaje específica o usar intervalos de tiempo no estándar-date_bin() (disponible en PostgreSQL 14 y superiores) es la mejor herramienta para el trabajo.

Entender cómo trabajar con fechas, zonas horarias y lógica de agrupamiento es esencial para cualquiera que haga análisis de datos o reportes serios en PostgreSQL.

Si está listo para perfeccionar sus habilidades en PostgreSQL con práctica del mundo real, consulte el curso SQL de la A a la Z en PostgreSQL en LearnSQL.es. Es un camino de aprendizaje práctico, paso a paso, que le ayudará a ir desde lo básico hasta técnicas más avanzadas como las cubiertas en este artículo.