example - mysql time()

MySQL CONVERT_TZ()

Question

Saludos a todos (de un principiante de MySQL)

Estoy tratando de configurar una base de datos que almacena los tiempos de alerta diaria según lo especificado por los usuarios. Por ejemplo, el usuario desea recibir una alerta si se cumple algún criterio cada día entre las 7:00 a.m. y las 7:30 a.m. Al tratar de implementar esto, necesito adaptar el horario de verano. Aquí está mi intento de solución:

1. Almacene la información de la zona horaria local del usuario (en forma larga, p. Ej., "EE. UU./Oriental") en una tabla (digamos userInfo), y las horas de alarma en otra tabla (digamos userAlarms).
2. Al consultar la tabla userAlarms, convierta la hora UTC en la hora local de los usuarios según lo especificado por la columna tz almacenada en la tabla CONVERT_TZ(UTC_TIME(), ''UTC'', userInfo.tz) través de CONVERT_TZ(UTC_TIME(), ''UTC'', userInfo.tz) .

Pregunta 1. Según tengo entendido, al especificar el nombre de la zona horaria (como US / Eastern) se debe tener en cuenta el horario de verano. Por ejemplo, llamar a CONVERT_TZ(''00:00:00'', ''UTC'', ''US/EASTERN'') el 1 de enero debería dar ''19: 00: 00'', pero el 1 de julio la llamada debería dar ''20: 00: 00 ''. ¿Estoy en lo correcto?

Pregunta 2. Si la Q1 es correcta, ¿debo actualizar constantemente la tabla de zona horaria de MySQL para mantener actualizadas las compensaciones de UTC de la zona horaria?

Pregunta 3. La muestra proporcionada en la documentación de MySQL SELECT CONVERT_TZ(''2004-01-01 12:00:00'',''GMT'',''MET'') produce " NULL " cuando se ejecuta en mi servidor. ¿Podría ser causado por no tener las tablas de zona horaria configuradas? ¿Cómo puedo verificar esto?

Answer 1

Encontré este hilo útil y decidí compartir la página del documento para importar esta información. Hice exactamente como se indica a continuación en CentOS y en RHEL y funcionó a la perfección. Ahora puedo usar la función CONVERT_TZ con argumentos como "GMT" y "US / Eastern".

De la documentación de MySQL, esta es la forma de importar la información de la tabla de zona horaria de MySQL:

http://dev.mysql.com/tech-resources/articles/4.1/time.html

Para todos los usuarios que ejecutan MySQL 4.1.3 o posterior en un sistema basado en Unix (recuerda que esto aún no funciona en los sistemas Windows):

Completa las tablas de zona horaria.

Para hacerlo, ejecute el programa mysql_tzinfo_to_sql , provisto con la distribución MySQL. mysql_tzinfo_to_sql lee los archivos de zona horaria del sistema operativo y genera declaraciones SQL de ellos. Las declaraciones SQL luego son procesadas por mysql, para cargar las tablas de zonas horarias.

Para ejecutar mysql_tzinfo_to_sql éxito, es necesario saber dónde se almacenan los archivos de zona horaria del sistema operativo de la máquina servidor; busca un directorio con un nombre similar a /usr/share/zoneinfo . Pase el nombre del directorio en la línea de comando a mysql_tzinfo_to_sql , y envíe la salida al programa mysql. Aquí hay un ejemplo.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Nota: El comando anterior asume que "mysql_tzinfo_to_sql" y "mysql" están en su camino. Si no lo son, tendrás que proporcionar la ruta completa a ambos al ejecutar el comando, o cambiar a la carpeta bin de mysql y usar un comando como ese:

shell> ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root mysql

Answer 2

Q1: Sí, CONVERT_TZ tiene en cuenta el horario de verano para usted. Esta información y la hora en que DST comienza / finaliza para cada zona horaria se almacena en las tablas time_zone_ *.

P2: Sí, como se indica en los documentos de mysql, la información de la zona horaria cambia según la política de cada área. Deberá actualizar las tablas time_zone_ * cada vez que ocurra un cambio. Apesta ser TI a veces, este es uno de ellos.

Q3: estas son las 5 tablas de zona horaria, consultalas para ver si tienen algo en ellas:

select * from mysql.time_zone_transition_type;
select * from mysql.time_zone_transition;
select * from mysql.time_zone_name;
select * from mysql.time_zone_leap_second;
select * from mysql.time_zone;

Answer 3

Si esto produce nulo, las tablas TZ no se han configurado:

SELECT CONVERT_TZ(now(),''US/Eastern'',''US/Central'');

Si no tiene configuradas las tablas de zona horaria, puede actualizar el desplazamiento de hora en la tabla de usuario y luego hacer:

select utc_timezone() - interval user_timezone_offset_in_hours hour
from userinfo a
where user_id = 999;

Sin embargo, todavía necesita una forma de actualizar la zona horaria del usuario.

Si está escribiendo esto para una aplicación web, puede obtener la zona horaria a través de javascript, aquí hay un article que describe cómo (no lo he intentado pero parece que funcionará).

Una pequeña explicación con respecto al ''intervalo'' anterior ...

Una de las construcciones más trucadas en MySQL es el uso de la palabra clave INTERVAL , que se muestra mejor con el ejemplo (el valor numérico puede ser una expresión o el valor del campo)

select now() today, now() - interval 1 day yesterday;
+---------------------+---------------------+
| today               | yesterday           |
+---------------------+---------------------+
| 2011-05-26 13:20:55 | 2011-05-25 13:20:55 |
+---------------------+---------------------+

Puedes agregarlos y restarlos de todos modos, por eso nunca me molesto con las funciones agregar / restar / convertir fecha / hora

select now() a, now() - interval 1 day + interval 4 hour + interval 8 minute b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-05-26 13:24:16 | 2011-05-25 17:32:16 |
+---------------------+---------------------+

Puedes usar números negativos (deberían ser buenos para los offsets negativos de zona horaria), estos son los mismos:

select now() - interval 1 month a, now() + interval -1 month b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-04-26 13:38:05 | 2011-04-26 13:38:05 |
+---------------------+---------------------+