Categorías
Bases de datos

MySQL y zonas horarias, consultas y optimización

MySQL es un motor de base de datos. Las zonas horarias o «timezones» son variaciones arbitrarias sobre la hora UTC que diferentes regiones adoptan para ajustar su actividad a las horas de luz del día.

Dos ideas que no tienen nada en común hasta que un desarrollo obliga a consultar información almacenada en MySQL relativa a fechas y horas que corresponden a una franja horaria distinta a la original en la que se guardaron los datos.

No pretendo hacer un how-to minucioso, sino más bien señalar los puntos que me han servido para evitar problemas en mis propios desarrollos. Lo iré revisando y ampliando con el tiempo.

Mi primera recomendación: guarda los datos en UTC. Si crees que puedes guardarlos en hora local y nunca vas a necesitar aplicar una máscara para presentarlos como si fueran de otra franja horaria (incluidas las variaciones del horario de verano…) este artículo no es para ti.

La segunda consideración: una consulta de este tipo tiene dos partes fundamentales, seleccionar los datos en UTC que corresponden al periodo que estamos consultando y devolverlos ya transformados a la zona horaria objetivo.

  1. Si quieres consultar un día completo de datos del timezone GMT -05:00, debemos construir la clausula WHERE adecuada:

    WHERE campoFecha >= ‘2014-02-19 05:00:00’ AND campoFecha <= ‘2014-02-20 04:59:59’

    Es muy recomendable evitar aplicar funciones a los campos en las clausulas WHERE porque además del procesamiento que supone calcular todos los registros (los que cumplen y los que no) antes de evaluar la condución, perderemos cualquier índice efectivo sobre esos campos.

  2. A la hora de aplicar la máscara al campo en la clausula SELECT para que muestre la hora transformada en la franja horaria que necesitamos, tenemos dos opciones bastante cómodas:
    1. Usar DATE_ADD y DATE_SUB (si la franja horaria presentase alguna modificación a los minutos puedes anidar estas funciones para modificar primero las horas y luego los minutos).
    2. La función que yo recomiendo CONVERT_TZ que si bien tiene alguna limitación en algún GMT minoritario es más cómoda de usar en las consultas, ya que permite operar con modificadores con minutos (-03:30) o bien con ALIAS,  que tendrán en cuenta los cambios de hora de verano / invierno en aquellos timezones que lo requieran.

Por último, una nota sobre agrupación por campos de fecha en relación a timezones. En el punto anterior describía dos opciones igual de válidas para aplicar una «máscara» a la fecha y que represente la franja horaria que deseamos.

¿Y si queremos agrupar cantidades «diarias» o «por horas» sobre esas fechas? es importante que al aplicar DATE_FORMAT lo hagamos al resultado de haber calculado la hora ya en el timezone objetivo y no antes, o cortaremos las horas y los días en base al valor original UTC, lo cual sería incorrecto. Dentro de DATE_FORMAT se pueden anidar las funciones anteriores o bien utilizar una subquery para primero obtener los datos con máscara y sobre eso aplicar un SELECT con un DATE_FORMAT sobre el campo fecha.

Un último consejo si no trabajas mucho con MySQL, asigna un alias al campo fecha creado que podrás utilizar en la clausula GROUP BY fácilmente.

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.