Existen muchas razones por las que es más conveniente tener una estructura de calendario como una tabla propiamente dicha en vez de estar generando fechas al vuelo en cada query o calculando valores en cada consulta hacia nuestra base de datos. Tiempos más cortos en nuestras consultas, reducción considerable en la complejidad de nuestros queries, uniformidad en la presentación de la información por dar algunos ejemplos.

Librerías como moment.js y  date-fns han significado una enorme mejora en la forma en que resolvemos muchos de los problemas asociados al manejo de fechas, sin embargo un calendario como una estructura dentro de nuestra base de datos a la cual podemos hacer joins y obtener un contexto inmediato de forma mucho más rápida y eficiente directamente dentro del mismo orígen de nuestra información.

Lo básico

De entrada para cada día en nuestra tabla tendremos los siguientes atributos:

  • Día del mes. La representación numérica del mes, un número entero del 1 al 12.
  • Día calendario. Representación numérica del día calendario, un número del 1 al 31. Este número variará según el mes y si es un año bisiesto.
  • Año calendario. Representación numérica del año.
  • Trimestre calendario. Representación numérica del trimestre del año relativo a la fecha. Un número del 1 al 4.
  • Nombre del día. El nombre común del día de la semana.
  • Día de la semana. Representación numérica del día de la semana. Un número del 1 al 7.
  • Nombre del mes. El nombre común del día de la semana.

Contexto adicional

Adicionalmente a lo básico de cualquier punto en el tiempo, una tabla calendario realmente brilla cuando agregamos todos los siguientes atributos y obtener de esta manera un contexto realmente útil en cualquier ámbito donde se le utilice:

  • Día de la semana en el mes. Ej.: El tercer martes del mes actual.
  • Día de la semana en el año. Ej.: El séptimo miércoles del año actual.
  • Día de la semana en el trimestre. Ej.: El quinto viernes del trimestre actual.
  • Día del trimestre. El número del día dentro del trimestre actual.
  • Día del año. El número del día dentro del año actual.
  • Semana del mes. El número de la semana dentro del mes actual.
  • Semana del trimestre. El número de la semana dentro del trimestre actual.
  • Semana del año. El número de la semana dentro del año actual.
  • Fecha inicial de la semana. La fecha inicial de la semana.
  • Fecha final de la semana. La fecha final de la semana.
  • Fecha inicial del mes. La fecha inicial del mes actual.
  • Fecha final del mes. La fecha final del mes actual.
  • Fecha inicial del trimestre. La fecha inicial del trimestre actual.
  • Fecha final del trimestre. La fecha final del trimestre actual.
  • Fecha inicial del año. La fecha inicial del año actual.
  • Fecha final del año. La fecha final del año actual.

Días hábiles

Dependiendo de la industria o actividad económica, un calendario que nos permita determinar con facilidad los días hábiles y por ende también los días inhábiles nos dará un valor agregado. Para este fin tomaremos en cuenta los siguientes atributos:

  • ¿Es día festivo? Nos indica si la fecha es un día festivo o no.
  • ¿Es época festiva? Nos indica si la fecha es parte de una época festiva o no. Ej.: Semana santa, fiestas de fin de año.
  • Nombre de la festividad. Si aplica, el nombre de la festividad.
  • Nombre de la época festiva. Si aplica, el nombre de la época festiva.
  • ¿Es día laboral entre semana? Nos indica si la fecha está comprendida entre Lunes y Viernes.
  • ¿Es día hábil? Combina la información de día entre semana y días / épocas festivas para determinar si es un día hábil.
  • Día hábil anterior. La fecha del día hábil inmediato anterior.
  • Día hábil siguiente. La fecha del día hábil inmediato siguiente.

Otros atributos

Finalmente, incluiremos algunos atributos adicionales que pueden agregar valor dependiendo de la actividad económica o industria donde se utilice esta información:

  • ¿Es año bisiesto? Indica si el año actual es año bisiesto.
  • Días en el mes. Representación numérica de la cantidad de días en el mes actual.
  • Fecha en formato legible. Una representación de la fecha en un formato fácilmente entendible para el usuario.

La tabla principal

Una vez definido el alcance de la información que vamos a contener en esta estructura, manos a la obra.

CREATE TABLE `calendario` (
    `fecha_calendario`          	DATE NOT NULL,
    `fecha_calendario_legible`  	VARCHAR(10) NOT NULL,
    `mes_calendario`            	TINYINT NOT NULL,
    `dia_calendario`            	TINYINT NOT NULL,
    `anio_calendario`           	SMALLINT NOT NULL,
    `trimestre_calendario`      	TINYINT NOT NULL,
    `dia_nombre`                	VARCHAR(9) NOT NULL,
    `dia_de_la_semana`          	TINYINT NOT NULL,
    `dia_de_la_semana_en_mes`   	TINYINT NOT NULL,
    `dia_de_la_semana_en_anio` 	 	TINYINT NOT NULL,
    `dia_de_la_semana_en_trimestre` TINYINT NOT NULL,
    `dia_del_trimestre`            	TINYINT NOT NULL,
    `dia_del_anio`               	SMALLINT NOT NULL,
    `semana_del_mes`             	TINYINT NOT NULL,
    `semana_del_trimestre`          TINYINT NOT NULL,
    `semana_del_anio`              	TINYINT NOT NULL,
    `mes_nombre`                	VARCHAR(10) NOT NULL,
    `semana_inicio`        			DATE NOT NULL,
    `semana_fin`         			DATE NOT NULL,
    `mes_inicio`       				DATE NOT NULL,
    `mes_fin`        				DATE NOT NULL,
    `trimestre_inicio`     			DATE NOT NULL,
    `trimestre_fin`      			DATE NOT NULL,
    `anio_inicio`        			DATE NOT NULL,
    `anio_fin`         				DATE NOT NULL,
    `es_feriado`                	BIT NOT NULL,
    `es_epoca_festiva`         		BIT NOT NULL,
    `feriado_nombre`              	VARCHAR(100) NULL,
    `epoca_festiva_nombre`       	VARCHAR(100) NULL,
    `es_dia_entre_semana`           BIT NOT NULL,
    `es_dia_habil`           		BIT NOT NULL,
    `dia_habil_anterior`     		DATE NULL,
    `dia_habil_siguiente`         	DATE NULL,
    `es_bisiesto`              		BIT NOT NULL,
    `dias_en_mes`             	    TINYINT NOT NULL,

    PRIMARY KEY (`fecha_calendario`)
)ENGINE=InnoDB;

Cargar fechas a la tabla

Definir la tabla y su estructura es meramente el inicio de nuestra tarea. Si bien podemos agregar manualmente algunos registros rápidamente nos daremos cuenta que es una tarea que consume mucho tiempo y se presta para errores. La forma más rápida de hacerlo es mediante un stored procedure.

Finalmente tenemos todas las piezas necesarias para armar en definitiva el rompecabezas. Tendremos una estructura en base de datos como ésta:

La estructura de nuestra tabla calendario

Y para fines de demostración inicial invoquemos nuestro stored procedure para una muestra pequeña de datos. Utilizaremos un rango de Noviembre 1, 2020 a Diciembre 31, 2020:

CALL sp_agregarFechas('2020-11-01', '2020-12-31');

¿Para qué tanto trabajo?

Habiendo llegado a este punto y a pesar de haber explicado al inicio acerca del por qué ponernos la tarea de elaborar una estructura como la que acabamos de construir, seguramente siempre surgirá la duda de la verdadera necesidad de este ejercicio si la amplia mayoría de lenguajes tienen funciones que hacen más o menos lo mismo que acabamos de insertar en nuestra tabla.

Cuando trabajamos en un entorno con miles, cientos de miles, incluso millones de registros cuya presentación se hace de forma lineal (un histograma) es cuando una estructura de calendario se vuelve particularmente conveniente, no sólo por cuestiones de tiempo sino en la reducción de complejidad en la elaboración de consultas hacia la base de datos. Es trivial determinar el número de semana en el año relativo a una fecha pero se complica enormemente cuando estamos haciendo una agrupación por número de semana en el año y existe un gap en la data que hace que nuestro query se "salte" esa semana que no existe en la data que estamos procesando. Si hacemos un LEFT JOIN entre la fecha de nuestro calendario y cualquier tabla que estemos evaluando, repentinamente veremos que los gaps desaparecen: la base para nuestro histograma, reporte o gráfica es nuestra tabla de calendario, donde todas las fechas existen y cualquier agrupación por día, semana, mes o año siempre retornarán resultados.

Adicionalmente está el tema de la eficiencia. Seguramente puede surgir el argumento de que nuestra tabla ocupará permanentemente espacio en disco, pero incluso si generamos la información para 200 años nos habremos tardado poco más de 10 segundos y generado 73,049 registros en total:

En un entorno donde la rapidez y el enorme beneficio de simplificar nuestras consultas hacia la base de datos es clave, 7.5 MiB (aproximadamente 7.86 MB) en disco no va a tener ningún impacto significativo en nuestra base de datos.

Veamos un ejemplo rápido. He creado una tabla aleatoria de pedidos con 10,000 registros y haremos un query inicial sin utilizar la tabla de calendario y luego haremos otro query utilizando la ayuda de dicha tabla para comparar los resultados.

Un query inicial nos muestra en forma descendente desde la fecha más reciente hacia abajo todos los pedidos hasta el inicio del año 2019. Ahora vamos un paso más allá y hagamos un total por día aún sin utilizar nuestra tabla calendario:

Como podemos observar, es algo trivial el ordenar la información media vez tengamos registros en la tabla. Sin embargo algo que a simple vista se puede notar es que los días no van en forma consecutiva. Si no existe un registro para un día cualquiera, sencillamente no aparece en nuestro query.

Existen formas de llenar esos "vacíos" en nuestras consultas pero luego de muchos años e incansables batallas perdidas he aprendido que algo tan sencillo como esto resulta ser la solución más sencilla y con menos dolores de cabeza:

La base de nuestro query es ahora la tabla calendario y cualquier tabla que necesitemos evaluar en función del tiempo únicamente es necesario hacer un LEFT JOIN entre los campos fecha y el motor de base de datos hace el resto del trabajo. Si bien es un ejemplo bastante sencillo, el concepto es bastante poderoso y flexible a la vez, lo que permite que esta solución escale de manera eficiente para escenarios más complejos. En un futuro evaluaremos otros escenarios.