MySQL
Manejo de zonas horarias
Buscar..
Observaciones
Cuando necesite manejar información de tiempo para una base de usuarios de todo el mundo en MySQL, use el tipo de datos TIMESTAMP en sus tablas.
Para cada usuario, almacene una columna de zona horaria de preferencia de usuario. VARCHAR (64) es un buen tipo de datos para esa columna. Cuando un usuario se registra para usar su sistema, solicite el valor de la zona horaria. El mío es el tiempo del Atlántico, America/Edmonton . El suyo puede o no ser Asia/Kolkata o Australia/NSW . Para una interfaz de usuario para esta configuración de preferencia de usuario, el software WordPress.org tiene un buen ejemplo.
Finalmente, siempre que establezca una conexión desde su programa host (Java, php, lo que sea) a su DBMS en nombre de un usuario, emita el comando SQL
SET SESSION time_zone='(whatever tz string the user gave you)'
Antes de manejar cualquier dato de usuario que involucre tiempos. Luego, todas las veces de TIMESTAMP que haya instalado se procesarán en la hora local del usuario.
Esto hará que todas las horas que ingresen a sus tablas se conviertan a UTC, y que todas las horas que salgan se traduzcan a local. Funciona correctamente para NOW () y CURDATE (). De nuevo, debe usar TIMESTAMP y no los tipos de datos DATETIME o DATE para esto.
Asegúrese de que el sistema operativo de su servidor y las zonas horarias de MySQL predeterminadas estén configuradas en UTC. Si no hace esto antes de comenzar a cargar información en su base de datos, será casi imposible de arreglar. Si utiliza un proveedor para ejecutar MySQL, insista en que lo hagan correctamente.
Recupere la fecha y hora actual en una zona horaria particular.
Esto obtiene el valor de NOW() en la hora local, en la hora estándar de la India, y luego nuevamente en UTC.
SELECT NOW();
SET time_zone='Asia/Kolkata';
SELECT NOW();
SET time_zone='UTC';
SELECT NOW();
Convierte un valor `DATE` o` DATETIME` almacenado en otra zona horaria.
Si tiene un DATE o DATETIME almacenado (en una columna en algún lugar) se almacenó con respecto a alguna zona horaria, pero en MySQL la zona horaria no se almacena con el valor. Por lo tanto, si desea convertirlo en otra zona horaria, puede hacerlo, pero debe conocer la zona horaria original. Usando CONVERT_TZ() hace la conversión. Este ejemplo muestra las filas vendidas en California en la hora local.
SELECT CONVERT_TZ(date_sold,'UTC','America/Los_Angeles') date_sold_local
FROM sales
WHERE state_sold = 'CA'
Recupere los valores almacenados de `TIMESTAMP` en una zona horaria particular
Esto es realmente fácil. Todos los valores de TIMESTAMP se almacenan en tiempo universal, y siempre se convierten a la configuración actual de time_zone cuando se procesan.
SET SESSION time_zone='America/Los_Angeles';
SELECT timestamp_sold
FROM sales
WHERE state_sold = 'CA'
¿Por qué es esto? TIMESTAMP valores de TIMESTAMP se basan en el venerable tipo de datos UNIX time_t . Esos sellos de tiempo de UNIX se almacenan como un número de segundos desde 1970-01-01 00:00:00 UTC.
Note que los valores de TIMESTAMP se almacenan en tiempo universal. DATE valores DATE y DATETIME se almacenan en cualquier hora local que estuviera vigente cuando se almacenaron.
¿Cuál es la configuración de zona horaria local de mi servidor?
Cada servidor tiene una configuración de zona horaria global predeterminada, configurada por el propietario de la máquina del servidor. Puede averiguar la configuración de la zona horaria actual de esta manera:
SELECT @@time_zone
Desafortunadamente, eso generalmente produce el valor SYSTEM , lo que significa que la hora de MySQL se rige por la configuración de zona horaria del sistema operativo del servidor.
Esta secuencia de consultas (sí, es un pirateo ) le devuelve la compensación en minutos entre la configuración de zona horaria del servidor y UTC.
CREATE TEMPORARY TABLE times (dt DATETIME, ts TIMESTAMP);
SET time_zone = 'UTC';
INSERT INTO times VALUES(NOW(), NOW());
SET time_zone = 'SYSTEM';
SELECT dt, ts, TIMESTAMPDIFF(MINUTE, dt, ts)offset FROM times;
DROP TEMPORARY TABLE times;
¿Como funciona esto? Las dos columnas en la tabla temporal con diferentes tipos de datos son la clave. DATETIME tipos de datos DATETIME siempre se almacenan en hora local en tablas y TIMESTAMP en UTC. Por lo tanto, la INSERT , realizada cuando la zona horaria está configurada en UTC, almacena dos valores de fecha / hora idénticos.
Luego, la instrucción SELECT, se realiza cuando la time_zone se establece en la hora local del servidor. TIMESTAMP s siempre se traducen de su formulario UTC almacenado a la hora local en las declaraciones SELECT. DATETIME s no son. Por lo tanto, la operación TIMESTAMPDIFF(MINUTE...) calcula la diferencia entre la hora local y la universal.
¿Qué valores de time_zone están disponibles en mi servidor?
Para obtener una lista de los posibles valores de time_zone en su instancia del servidor MySQL, use este comando.
SELECT mysql.time_zone_name.name
Normalmente, esto muestra la lista de ZoneInfo de zonas horarias mantenida por Paul Eggert en la Autoridad de Números Asignados de Internet . En todo el mundo hay aproximadamente 600 zonas horarias.
Los sistemas operativos similares a Unix (por ejemplo, distribuciones de Linux, distribuciones BSD y distribuciones modernas de Mac OS) reciben actualizaciones de rutina. La instalación de estas actualizaciones en un sistema operativo permite que las instancias de MySQL que se ejecutan allí rastreen los cambios en la zona horaria y los cambios de horario de verano / horario estándar.
Si obtiene una lista mucho más corta de nombres de zona horaria, su servidor está configurado de forma incompleta o se está ejecutando en Windows. Aquí hay instrucciones para que el administrador de su servidor instale y mantenga la lista de ZoneInfo.
