Search code examples
mysqldatetimetimezonezoneinfo

Timezone is wrong in database

I have made a date with the CURRENT_TIMESTAMP function in phpmyadmin. And the output value of the hours is wrong by 1hour. I have tried changing it in mysql using the SET TIME_ZONE = '+01:00';

However it will still submit the date with 1 hour earlier. Any suggestions?

Solution

  • You're touching a complex topic.

    When a column of a table has the TIMESTAMP data type, all values are stored in UTC after translating from your current time_zone setting to UTC. When you retrieve values from those columns they are translated back to your current time_zone. This is handy because you can present datestamps to users globally in their own local time by treating the time_zone as a user preference.

    When a column has the DATETIME data type, the translations do not occur.

    Here's an example:

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


SET time_zone = 'UTC';
INSERT INTO t1 (ts, dt) VALUES (NOW(), NOW());
SET time_zone = 'Europe/Paris';
INSERT INTO t1 (ts, dt) VALUES (NOW(), NOW());


SET time_zone = 'UTC';
SELECT  * FROM t1;    /* notice differences presentation of ts columns, ds stay the same */
SET time_zone = 'Europe/Paris';
SELECT  * FROM t1;    /* again */
SET time_zone = 'America/Halifax';
SELECT  * FROM t1;    /* again */

    Pro tip: If you use named time zones Europe/Paris you'll automatically get the benefit of the daylight time rules for the locale you chose. The rules, known as the zoneinfo database, are maintained by IANA. Many Linux distributions include changes to the zoneinfo database in their regular updates.