Uso práctico de las funciones de fecha y hora en el análisis de datos

Lección 11.2 · Tiempo de lectura: ~11 min · Tareas de práctica ↓

Esta lección se centra en el uso práctico de las funciones de fecha y hora de SQL en la analítica. Aprenderás a extraer periodos de los campos temporales, agregar datos por día y por mes, calcular intervalos entre eventos y construir informes útiles basados en métricas temporales. Al terminar, podrás analizar con seguridad la evolución en el tiempo en la base de datos Sakila.

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

En analítica no basta con mostrar payment_date o rental_date. En la práctica hay que responder preguntas como: cómo cambia la actividad de un día a otro, en qué horas hay más transacciones, cuánto tiempo pasa entre el alquiler y la devolución, y si hay picos estacionales.

Por qué importan las funciones de fecha y hora en la analítica

Casi todos los informes tienen una dimensión temporal. Aunque la pregunta de negocio sea "cuántas ventas" o "cuántos clientes", en la práctica casi siempre necesitas un contexto temporal: por día, semana, mes, trimestre o un periodo concreto.

Las funciones de fecha y hora te ayudan a:

• extraer la granularidad necesaria (día, mes, hora);
• agregar métricas a lo largo del tiempo;
• comparar periodos entre sí;
• calcular la duración de los procesos;
• detectar subidas y caídas anómalas.

Las funciones que más se usan

En MySQL, estas funciones son especialmente útiles para la analítica práctica:

• DATE(): obtiene solo la fecha de un DATETIME;
• YEAR(), MONTH(), DAY(): extraen partes de la fecha;
• HOUR(): sirve para analizar la actividad por horas;
• DATE_FORMAT(): construye una clave temporal cómoda;
• TIMESTAMPDIFF(): calcula el intervalo entre dos momentos;
• DATEDIFF(): calcula la diferencia en días.

Veamos casos prácticos con los datos de Sakila.

Agregar los pagos por día

Un primer caso práctico es ver cómo cambia el volumen de pagos de un día a otro.

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 la evolución diaria del número de pagos y del importe de los ingresos.

Este informe es útil como capa base para vigilar la actividad y detectar cambios bruscos.

Comparación mensual con DATE_FORMAT

Cuando necesitas una vista más compacta, agrega los 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 cómodo tanto para ordenar como para visualizar en herramientas de BI.

Si te quedas solo con el número de mes sin el año, los mismos meses de años distintos se juntarán en un único grupo.

Análisis de la actividad por horas

Una pregunta práctica habitual es: ¿a qué horas realizan los usuarios 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 la actividad por hora del día.

Esto ayuda a planificar la carga, elegir el momento de las campañas y organizar los turnos de trabajo.

Calcular la duración de los alquileres

Las funciones de tiempo se usan 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, conviene agregar la duración con la media y la mediana (si tu SGBD admite funciones de mediana).

Informe práctico: tiempo medio 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 media de los alquileres por día de la semana.

Este informe ayuda a identificar patrones de comportamiento y ajustar las reglas operativas según el día.

Comparar el periodo actual con el anterior

En la analítica real no solo importa calcular métricas, sino también comparar periodos. Incluso una simple comparación de dos rangos aporta 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 amplía fácilmente a comparaciones semana contra semana, mes contra mes y trimestre contra trimestre.

Recomendaciones prácticas

• Define de antemano la granularidad necesaria: día, semana, mes u hora.
• Para ordenar los periodos de forma estable, usa un formato que se ordene alfabéticamente (YYYY-MM).
• Al calcular intervalos, filtra explícitamente los eventos incompletos (return_date IS NOT NULL).
• Comprueba la zona horaria de los datos de origen al analizar la actividad por horas.
• Usa límites claros con >= y < al comparar periodos para evitar solapamientos.

Puntos clave de esta lección:

• Las funciones de fecha y hora de SQL son esenciales para analizar tendencias y estacionalidad en la práctica.
• DATE, DATE_FORMAT, HOUR, TIMESTAMPDIFF y DATEDIFF cubren la mayoría de las tareas reales.
• La granularidad temporal influye directamente en cómo se deben interpretar las métricas.
• Analizar los intervalos entre eventos ayuda a medir la eficiencia de los procesos.
• Incluso las comparaciones de periodos más sencillas aportan señales valiosas para tomar decisiones.

Preguntas frecuentes

¿Por qué >= inicio y < fin suele ser mejor que BETWEEN?

Porque este formato da intervalos claros y sin solapamientos, sobre todo con DATETIME. Reduce el riesgo de contar dos veces los valores de los límites.

¿Cuándo usar DATE_FORMAT y cuándo YEAR() y MONTH()?

DATE_FORMAT es cómodo para obtener claves listas para los informes (por ejemplo, 2025-08). YEAR() y MONTH() son útiles cuando necesitas tratar el año y el mes por separado o hacer cálculos adicionales.

¿Qué es lo que más a menudo estropea el análisis temporal?

Los problemas típicos son las zonas horarias mezcladas, una granularidad equivocada, los registros incompletos (NULL en return_date) y unos límites de periodo poco claros.

Preguntas de entrevista

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

DATEDIFF() devuelve la diferencia en días entre dos fechas. TIMESTAMPDIFF() permite elegir la unidad (horas, minutos, días, etc.) y es mejor para analizar intervalos con más precisión.

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

Porque la granularidad define la interpretación: la analítica diaria muestra las fluctuaciones operativas, mientras que la mensual muestra la tendencia. Un nivel de agregación equivocado puede ocultar patrones importantes.

¿Cómo validarías un informe temporal antes de publicarlo?

Comprobaría los límites de los periodos, las suposiciones sobre la zona horaria, el tratamiento de NULL y que no haya intervalos solapados, y después cuadraría los totales con una muestra de control de los datos en bruto.