TimeZone not set correctly

Question

I have a problem, I'm using mysql with PHP and I have in my database timestamp columns that are automatically set on update by the current timestamp. But when I'm trying to insert rows into the table the rows are added with timestamp different from my current time (before two hours) I have the EST timezone.

I've checked the timezone for the database and it is System and when I searched for that I read that it depeneds on the machine that is connecting, but my machine has EST time, so can anyone explain why I'm having this? and what can I do to set the current timestamp for my database to EST?

possible duplicate of [How to set time zone of mysql?](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql) — Jerodev, Jan 09 '15 at 15:14
MySQL reads the system timezone by default. If you need to change it you can do it via the command line parameters outlined here. http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html — geggleto, Jan 09 '15 at 15:15
When you are inserting rows in table, are using PHP functions to set value? e.g. date(); If that is the case check web server timezone using phpinfo(). — v2solutions.com, Jan 09 '15 at 15:17
I'm relaying on mysql to insert the timestamp for me on update. — Lisa, Jan 09 '15 at 15:23
No this is not a duplicate, I want to know what does SYSTEM timezone means? if it is not my machine ? — Lisa, Jan 09 '15 at 15:26
@Lisa: `time_zone=SYSTEM` means that the time_zone for the database connection is derived from the host that the MySQL **server** is running on, *not* the host that your client is running on. (See my answer.) — spencer7593, Jan 09 '15 at 15:59

score 0 · Answer 1 · answered Jan 09 '15 at 15:21

0

I've personally found it to be a lot more reliable to set the timezone in PHP, then grab the timestamp there and pass this to your MySQL query.

Something like:

date_default_timezone_set('America/New_York'); 
$curtimestamp = date('Y-m-d G:i:s', time());

Then use the value of $curtimestamp in your queries.

answered Jan 09 '15 at 15:21

osuddeth

152
9

I want to know why SYSTEM is returning a different timestamp from the one on my machine? – Lisa Jan 09 '15 at 15:25
Because MySQL handles time zones in an odd way. See my comment above - I've found it a lot more reliable to manage time zone stuff with PHP. Here's a link that might help you on the MySQL side, though. http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql – osuddeth Jan 09 '15 at 15:29

spencer7593 · Answer 2 · 2015-01-09T15:56:13.193

The documentation may have been unclear.

time_zone=SYSTEM means that the MySQL Server time zone is derived from the host that the MySQL Server is running on, not the client system.

So, if the time zone on the MySQL server host is different from the time zone of the client host, that explains the behavior that you observe.

Each client (database session) has its own time_zone setting. When the connection is established, the setting is derived from the "default" time zone for the MySQL server.

The setting of this variable determines how DATETIME and TIMESTAMP values are interpreted, when values are sent to the database from the client, AND when existing values are pulled from the database.

A client can issue a SET time_zone statement to change the time_zone for the database session, e.g.

SET time_zone=CST6CDT

(Note that to use named values for time zones, the time_zone% tables in the mysql database must be populated.)

To set the "default" time_zone that will be used for new database connections, you can set the "global" time_zone variable:

SET GLOBAL time_zone=

(This change requires SUPER privilege, and it impacts ALL new database connections to the entire instance. Normally, this value is set in the MySQL server configuration file.)

For TIMESTAMP columns, MySQL stores those as UTC. The values returned to the client depend on the setting of the client time_zone. As a simple demonstration:

mysql>     CREATE TABLE mytest
    ->     ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    ->     , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ->     );
Query OK, 0 rows affected (0.11 sec)

mysql>     SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +00:00 |
+---------------+--------+
1 row in set (0.00 sec)

mysql>     INSERT INTO mytest (id) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>     SELECT * FROM mytest;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2015-01-09 15:41:25 |
+----+---------------------+
1 row in set (0.00 sec)

mysql>     SET time_zone = 'CST6CDT';
Query OK, 0 rows affected (0.00 sec)

mysql>     SELECT * FROM mytest;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2015-01-09 09:41:25 |
+----+---------------------+
1 row in set (0.00 sec)

mysql>     INSERT INTO mytest (id) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>     SELECT * FROM mytest;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2015-01-09 09:41:25 |
|  2 | 2015-01-09 09:41:25 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql>     SET time_zone = 'EST5EDT';
Query OK, 0 rows affected (0.00 sec)

mysql>     SELECT * FROM mytest;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2015-01-09 10:41:25 |
|  2 | 2015-01-09 10:41:25 |
+----+---------------------+
2 rows in set (0.00 sec)

TimeZone not set correctly

2 Answers2