CODEWITHSUNDEEP
mysqlsql
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

MySQL and time zones

Assume the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I run the following SQL:

SET time_zone = '+00:00';
INSERT INTO `test` (`stamp`) VALUES (CURRENT_TIMESTAMP);

The value in test is my machine's local time instead of UTC.

According to MySQL site

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

What am I doing wrong? I want to store my records with UTC date/time stamp and retrieve them in the user's local time zone.

Upvotes: 1

Views: 171

Answers (1)

Andrew Landsverk
Andrew Landsverk

Reputation: 673

According to the MySQL 5.5 documentation using the TIMESTAMP data type will default to using the server's time zone unless you change the variable from the client side.

From: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

Upvotes: 0

Related Questions