Timestamps Y Zonas Horarias (Time Zones) En MySQL

En MySQL existen dos tipos para almacenar fechas y horas: DATETIME y TIMESTAMP. La diferencia entre estos dos, es que el TIMESTAMP convierte la fecha de la zona horaria actual a UTC, o sea, guarda la fecha en un formato universal e internacional, que más o menos coincide con el meridiano de Greenwich.

Instalar MySQL Time Zones En Windows

1. Entrar al sitio Time Zone Description Tables y descargar el archivo .zip.
2. Descomprimir el archivo .zip.
3. Esta carpeta tiene archivo .frm, .myd y .myi. Estos archivos tenemos que copiarlos (o sobreescribirlos) en la carpeta c:\mysql\data\mysql\ (el inicio de la ruta puede ser diferente).
4. Reiniciar el servidor de MySQL.

Si todo ha salido bien, al correr la siguiente consulta deben aparecer más de 500 filas.

SELECT * FROM mysql.time_zone_name;

Columnas TIMESTAMP

La primera columna de tipo TIMESTAMP en tablas con una o más columnas TIMESTAMP tiene truco. Esa columna (la primera) siempre se actualiza al valor CURRENT_TIMESTAMP en un INSERT o un UPDATE si no se le especifica explícitamente otro valor.

-- tabla con columnas TIMESTAMP
CREATE TABLE example1 (
  w INT,
  x DATETIME,
  y TIMESTAMP,
  z TIMESTAMP
);

INSERT INTO example1
VALUES (1, '2010#10#10 00#00#00', '2010#10#10 00#00#00', '2010#10#10 00#00#00');

-- 1, '2010-10-10 00:00:00', '2010-10-10 00:00:00', '2010-10-10 00:00:00'
SELECT *
FROM example1;

-- la primera columna TIMESTAMP también cambia de valor
UPDATE example1
SET w = 2;

-- 2, '2010-10-10 00:00:00', '2008-06-01 14:30:40', '2010-10-10 00:00:00'
SELECT *
FROM example1;

Para evitar que la primera columna TIMESTAMP se actualice, podemos modificar la estructura de la tabla y declara esa columna con un DEFAULT CURRENT_TIMESTAMP lo que evita un ON UPDATE CURRENT_TIMESTAMP implícito.

-- tabla con columnas TIMESTAMP
CREATE TABLE example2 (
  w INT,
  x DATETIME,
  y TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  z TIMESTAMP
);

INSERT INTO example2
VALUES (1, '2010#10#10 00#00#00', '2010#10#10 00#00#00', '2010#10#10 00#00#00');

-- 1, '2010-10-10 00:00:00', '2010-10-10 00:00:00', '2010-10-10 00:00:00'
SELECT *
FROM example2;

-- la primera columna TIMESTAMP no cambia de valor
UPDATE example2
SET w = 2;

-- 2, '2010-10-10 00:00:00', '2010-10-10 00:00:00', '2010-10-10 00:00:00'
SELECT *
FROM example2;

DATETIME vs TIMESTAMP

Si jugamos a cambiar de zona horaria, la columna DATETIME permanecerá constante, pero la columna TIMESTAMP se ajusta a la nueva zona horaria.

-- tabla con columnas DATETIME y TIMESTAMP
CREATE TABLE example3 (
  x DATETIME,
  y TIMESTAMP DEFAULT 0
);

-- cambiar a zona horaria de la Ciudad de México
SET GLOBAL time_zone = 'America/Mexico_City';

INSERT INTO example3
VALUES ('2010#10#10 00#00#00', '2010#10#10 00#00#00');

-- '2010-10-10 00:00:00', '2010-10-10 00:00:00'
-- iguales
SELECT *
FROM example3;

-- 1286686823, 1286686823
-- iguales
SELECT UNIX_TIMESTAMP(x), UNIX_TIMESTAMP(y)
FROM example3;

-- cambiar a zona horaria de Madrid
SET GLOBAL time_zone = 'Europe/Madrid';

-- '2010-10-10 00:00:00', '2010-10-10 07:00:00'
-- columna DATETIME permanece constante (no cambia)
-- columna TIMESTAMP se ajusta a la zona horaria Europe/Madrid
SELECT *
FROM example3;

-- 1286661623, 1286686823
-- columna DATETIME primero se convierte de Europe/Madrid a UTC
-- columna TIMESTAMP sigue valiendo el mismo UNIX_TIMESTAMP
SELECT UNIX_TIMESTAMP(x), UNIX_TIMESTAMP(y)
FROM example3;

-- convertir la hora de Europe/Madrid a America/Mexico_City
-- '2010-10-10 00:00:00', '2010-10-10 00:00:00'
-- iguales otra vez
SELECT x, CONVERT_TZ(y,'Europe/Madrid','America/Mexico_City')
FROM example3;

SELECT x, CONVERT_TZ(y,'Europe/Madrid','SYSTEM')
FROM example3;

La columna TIMESTAMP funciona como la mayoría de nuestros dispositivos eléctricos (computadora, teléfono celular, PALM, iPod, ..) y lo puede comprobar si le cambia la zona horaria.

SYSTEM No Siempre Es Lo Mismo Que America/Mexico_City

La “zona horaria” SYSTEM representa la hora y la zona horaria del sistema operativo, en mi caso Windows XP. MySQL sigue esta zona horaria por defecto.

Pero si comparamos los UNIX_TIMESTAMP de una fecha almacenada con zona horaria SYSTEM y una fecha almacenada con zona horaria America/Mexico_City, podemos ver que existe una diferencia de segundos.

CREATE TABLE example4 (
  x TIMESTAMP DEFAULT 0
);

-- cambiar a zona horaria del sistema operativo
SET GLOBAL time_zone = 'SYSTEM';

INSERT INTO example4
VALUES ('2010#10#10 00#00#00');

-- cambiar a zona horaria de la Ciudad de México
SET GLOBAL time_zone = 'America/Mexico_City';

INSERT INTO example4
VALUES ('2010#10#10 00#00#00');

-- '2010-10-09 23:59:37'
-- '2010-10-10 00:00:00'
-- 23 segundos de diferencia
SELECT *
FROM example4;

Lo mismo sucede con PHP y MySQL. El TIMESTAMP de MySQL sigue las reglas del UTC y le suma algunos segundos extra para tener una hora más exacta.

Conocer La Zona Horaria De MySQL

Basta imprimir las variables @@global.time_zone y @@session.time_zone para conocer la zona horaria de nuestro manejador MySQL.

SELECT @@global.time_zone, @@session.time_zone;