MySQL: Agrupar Columna Timestamp por Mes y Año

En blogs y foros, a menudo podemos ver una lista de enlaces que muestran en qué mes y año cuántas publicaciones se han escrito. Si hace clic en uno de estos enlaces, obtendrá los artículos y publicaciones relevantes solo para este mes.

Pero, ¿cómo determinar la cantidad de artículos por mes? Finalmente, tenemos los artículos en nuestra tabla MySQL ordenados cronológicamente con una columna TIMESTAMP o DATE y un "GROUP BY columna_fecha" no devolvería el número de artículos por mes o año, sino el número de publicaciones por día específico o incluso por hora específica -y no queremos tener un resultado así.

La clave está en el uso de las funciones MySQL YEAR() y MONTH(), que nos permiten devolver el año respectivamente el mes de una columna TIMESTAMP, DATETIME o DATE, extrayendo esta parte de la fecha.

Con esto, nuestro GROUP BY podría verse así:

GROUP BY YEAR(dat), MONTH(dat)

De este modo agrupamos la columna "dat" por año y mes.

Determinar el Número de Registros de una Tabla por Mes

Veamos un caso de uso específico. A continuación se muestra un ejemplo de una consulta SELECT completa:

SELECT COUNT(id), RPAD('0', 2, MONTH(dat)), YEAR(dat) FROM tab
GROUP BY YEAR(dat), MONTH(dat);

En consecuencia, contamos el ID (o opcionalmente cualquier otra asignación única de nuestros datos) y en el mismo paso leemos el mes y el año de las aportaciones correspondientes. El RPAD garantiza que nuestro mes se lea con ceros a la izquierda, para que no tengamos que formatear el número después.

Un posible resultado de esta consulta podría ser similar al siguiente:

NÚMERO   MES     AÑO
12       01      2024
37       02      2024
83       03      2024
78       04      2024
95       05      2024

Con PHP, estos números se pueden formatear simplemente para que podamos obtener una representación como "01/2024 (12)", por ejemplo, o como desee. Por supuesto, también puede añadir un enlace en cada fila, para que se puedan mostrar los artículos correspondientes.

Determinar el Número de Registros de una Tabla por Año

Por supuesto, también podemos estructurar nuestra consulta de manera que cuentemos los elementos por año en lugar de por mes. Sólo tenemos que omitir la parte del mes para conseguir eso:

SELECT COUNT(id), YEAR(dat) FROM tab
GROUP BY YEAR(dat);

Un posible resultado entonces podría verse así:

NÚMERO  AÑO
67      2021
39      2022
73      2023
29      2024

Esto podría ser una buena idea, por ejemplo, en el caso de que tengamos una página web con muy pocos artículos, por lo que una resolución mensual sería demasiado pequeña.