Lesión 11.2 · Tiempo de lectura: ~11 min

Esta lección se centra en el uso práctico de funciones de fecha y hora en SQL para análisis. Aprenderás a extraer períodos de campos temporales, agregar datos por día y mes, calcular intervalos entre eventos y construir informes de trabajo basados en métricas de tiempo. Al final de esta lección, podrás analizar con confianza las dinámicas basadas en el tiempo en la base de datos Sakila.

Uso Práctico de Funciones de Fecha y Hora para Análisis de Datos

En la lección anterior, cubrimos el procesamiento práctico de cadenas. Ahora pasamos a otro tipo de dato que aparece en casi todas las tareas reales: fecha y hora.

En análisis, no es suficiente con mostrar payment_date o rental_date. En la práctica, necesitas responder preguntas como: cómo cambia la actividad por día, qué horas tienen más transacciones, cuánto tiempo pasa desde el alquiler hasta la devolución, y si hay picos estacionales.

Por Qué Importan las Funciones de Fecha y Hora en Análisis

Casi todos los informes tienen una dimensión temporal. Incluso si la pregunta comercial es “cuántas ventas” o “cuántos clientes”, en la práctica, generalmente necesitas contexto temporal: por día, semana, mes, trimestre o un período específico.

Las funciones de fecha y hora te ayudan a:

• extraer la granularidad requerida (día, mes, hora);
• agregar métricas a lo largo del tiempo;
• comparar períodos entre sí;
• calcular duraciones de procesos;
• detectar picos y caídas anormales.

Funciones Clave Usadas Más Frecuentemente

En MySQL, estas funciones son especialmente útiles para análisis prácticos:

• DATE() - obtener solo la fecha de DATETIME;
• YEAR(), MONTH(), DAY() - extraer partes de la fecha;
• HOUR() - analizar actividad horaria;
• DATE_FORMAT() - construir una clave de tiempo conveniente;
• TIMESTAMPDIFF() - calcular el intervalo entre dos momentos;
• DATEDIFF() - diferencia en días.

Vamos a recorrer escenarios prácticos utilizando datos de Sakila.

Agregando Pagos por Día

Un primer escenario práctico es ver cómo cambia el volumen de pagos por día.

SELECT
   DATE(payment_date) AS payment_day,
   COUNT(*) AS payments_count,
   SUM(amount) AS total_amount
FROM payment
GROUP BY DATE(payment_date)
ORDER BY payment_day;

Resultado: obtienes dinámicas diarias de la cantidad de pagos y el monto de ingresos.

Este informe es útil como una capa base para el monitoreo de actividad y la detección de cambios repentinos.

Comparación Mensual con DATE_FORMAT

Cuando necesitas una vista más compacta, agrega datos por mes.

SELECT
   DATE_FORMAT(payment_date, '%Y-%m') AS year_month,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS revenue
FROM payment
GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
ORDER BY year_month;

Nota: %Y-%m es conveniente tanto para ordenar como para visualización en BI.

Si mantienes solo el número del mes sin el año, los mismos meses de diferentes años se agruparán en un solo grupo.

Análisis de Actividad Horaria

Una pregunta práctica común es: ¿en qué horas los usuarios realizan más acciones?

SELECT
   HOUR(payment_date) AS payment_hour,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS total_amount
FROM payment
GROUP BY HOUR(payment_date)
ORDER BY payment_hour;

Resultado: obtienes la distribución de actividad por hora del día.

Esto ayuda con la planificación de carga, el momento de las campañas y la programación operativa.

Calculando la Duración del Alquiler

Las funciones de tiempo se utilizan a menudo para analizar el ciclo de vida de los eventos. En Sakila, puedes medir cuántas horas pasan entre el alquiler y la devolución.

SELECT
   rental_id,
   rental_date,
   return_date,
   TIMESTAMPDIFF(HOUR, rental_date, return_date) AS rental_duration_hours
FROM rental
WHERE return_date IS NOT NULL
ORDER BY rental_duration_hours DESC
LIMIT 10;

Resultado: la consulta muestra los alquileres completados más largos.

Para una vista resumida, es útil agregar la duración por promedio y mediana (si tu DBMS soporta funciones de mediana).

Informe Práctico: Tiempo Promedio de Devolución por Día de la Semana

Ahora combinemos funciones de tiempo y agregación en una consulta aplicada.

SELECT
   DAYOFWEEK(rental_date) AS week_day,
   COUNT(*) AS rentals_count,
   ROUND(AVG(TIMESTAMPDIFF(HOUR, rental_date, return_date)), 2) AS avg_return_hours
FROM rental
WHERE return_date IS NOT NULL
GROUP BY DAYOFWEEK(rental_date)
ORDER BY week_day;

Resultado: obtienes la duración promedio de alquiler por día de la semana.

Este informe ayuda a identificar patrones de comportamiento y ajustar las reglas operativas por día.

Comparando Períodos Actuales y Anteriores

En análisis reales, es importante no solo calcular métricas, sino también comparar períodos. Incluso una simple comparación de dos rangos proporciona una señal útil.

SELECT
   CASE
      WHEN payment_date >= '2005-07-01' AND payment_date < '2005-08-01' THEN 'period_1'
      WHEN payment_date >= '2005-08-01' AND payment_date < '2005-09-01' THEN 'period_2'
   END AS period_label,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS revenue
FROM payment
WHERE payment_date >= '2005-07-01'
  AND payment_date < '2005-09-01'
GROUP BY period_label
ORDER BY period_label;

Nota: este patrón se escala fácilmente a comparaciones semana a semana, mes a mes y trimestre a trimestre.

Recomendaciones Prácticas

• Define la granularidad requerida de antemano: día, semana, mes o hora.
• Para un ordenamiento estable de períodos, utiliza un formato ordenable lexicográficamente (YYYY-MM).
• Filtra explícitamente eventos incompletos (return_date IS NOT NULL) al calcular intervalos.
• Verifica la zona horaria de origen al analizar la actividad horaria.
• Usa límites claros >= y < para la comparación de períodos para evitar superposiciones.

Conclusiones clave de esta lección:

• Las funciones de fecha y hora en SQL son esenciales para el análisis práctico de tendencias y estacionalidad.
• DATE, DATE_FORMAT, HOUR, TIMESTAMPDIFF y DATEDIFF cubren la mayoría de las tareas del mundo real.
• La granularidad temporal afecta directamente cómo deben ser interpretadas las métricas.
• El análisis de intervalos de eventos ayuda a medir la eficiencia de los procesos.
• Incluso comparaciones simples de períodos proporcionan señales valiosas para la toma de decisiones.

Preguntas Frecuentes

¿Por qué es >= start y < end a menudo mejor que BETWEEN?

Porque este formato proporciona intervalos claros y no superpuestos, especialmente para DATETIME. Reduce el riesgo de conteo doble en los límites.

¿Cuándo debo usar DATE_FORMAT en lugar de YEAR() y MONTH()?

DATE_FORMAT es conveniente para claves de informes listas para usar (por ejemplo, 2025-08). YEAR() y MONTH() son útiles cuando necesitas lógica separada de año/mes o cálculos adicionales.

¿Qué es lo que más a menudo rompe el análisis basado en tiempo?

Los problemas típicos son zonas horarias mezcladas, granularidad incorrecta, registros incompletos (NULL en return_date), y límites de período poco claros.

Preguntas de Entrevista

¿Cómo explicarías la diferencia entre DATEDIFF() y TIMESTAMPDIFF()?

DATEDIFF() devuelve la diferencia en días entre fechas. TIMESTAMPDIFF() te permite elegir una unidad (horas, minutos, días, etc.) y es mejor para un análisis de intervalos más preciso.

¿Por qué es importante elegir la granularidad temporal correcta en los informes?

Porque la granularidad define la interpretación: el análisis diario resalta fluctuaciones operativas, mientras que el análisis mensual resalta tendencias. Un nivel de agregación incorrecto puede ocultar patrones importantes.

¿Cómo validarías un informe basado en el tiempo antes de su publicación?

Verificaría los límites de período, las suposiciones de zona horaria, el manejo de NULL, la ausencia de superposición de intervalos, y luego reconciliaría los totales con una muestra de control de los datos en bruto.

En la próxima lección, pasaremos a técnicas de transformación de datos para análisis y veremos cómo combinar cálculos basados en tiempo y condicionales en una sola consulta.