CODEWITHSUNDEEP
phpmysqltimezone
Andrew Phillips
Andrew Phillips

Reputation: 664

MySQL database timezone isn't being set correctly - why?

I have a MySQL database hosted via a Cloud service provider. The timezone is different to my local timezone. I'm trying to set the timezone correctly ..

Here's my PHP code I'm using when I want to get a new connection to a MySQL database:

static function get() {
  if (self::$instance!=null) return self::$instance;

  try {
    self::$instance = new Database("mysql:host=".self::$host.";dbname=".self::$dbname, self::$user, self::$pass);
    self::$instance->exec("SET NAMES 'utf8';");  //Use utf8 encoding for special characters
    self::$instance->exec("SET time_zone = '+01:00';"); // set to local timezone   
    return self::$instance;
  }
  catch(PDOException $e) {
      print $e->getMessage();
      return null;
  }
}

Whenever I insert new entries into a table, the timestamp is still set to the timezone of the cloud service provider. Can anyone help see what I've done wrong?

Upvotes: 2

Views: 377

Answers (1)

madebydavid
madebydavid

Reputation: 6517

Are you sure that you are using the TIMESTAMP type for the field? MySQL's timestamp type stores the data as UTC and the converts to the current session time zone when you read the data. The DATETIME type does not do this, it has no time zone information and so cannot be converted. See the docs relating to this topic here.

For example:

mysql> CREATE TABLE test (some_date DATETIME, created timestamp);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test (some_date) VALUES (NOW());
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test;
+---------------------+---------------------+
| some_date           | created             |
+---------------------+---------------------+
| 2013-11-05 13:14:56 | 2013-11-05 13:14:56 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = '+4:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---------------------+---------------------+
| some_date           | created             |
+---------------------+---------------------+
| 2013-11-05 13:14:56 | 2013-11-05 17:14:56 |
+---------------------+---------------------+

In the above example, only the TIMESTAMP column is affected by the change in the session time_zone. If you need to convert the DATETIME column to a different time zone then you should use the CONVERT_TZ function when you read the data - you specify the time zone that the data is in and the time zone that you would like it converted to.

Upvotes: 2

Related Questions