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.
- 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.
- 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:
- 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).
- 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.