-1

I have a few columns with the data type TIMESTAMP. Initially the timezone was default. So I have added the values like this.

mysql> create table test (t1 timestamp, t2 datetime);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values ('2018-08-08 18:09:36','2018-08-08 18:09:36');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2018-08-08 18:09:36 | 2018-08-08 18:09:36 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | SYSTEM             | SYSTEM              |
    +--------------------+---------------------+

Then in the mysqld.cnf file, I have added the following variable to make the timezone as IST.

default_time_zone = +05:30

Then I logged into the MySQL and saw the data, but this time the t1 column has changed the values with 5Hr and 30mins advanced.

mysql> select * from test;
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2018-08-08 23:39:36 | 2018-08-08 18:09:36 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +05:30             | +05:30              |
+--------------------+---------------------+

I know TIMESTAMP by default store the value in UTC format, when retrieving the data it'll convert to the End User's Session TimeZone.

Why the TIMESTAMP alone changed? DATETIME is still same. In this case, my all the reports are showing the incorrect value with + 5.30Hrs.

  • Timestamp column value was NOT changed. It is always UTC. So zone deviation is substracted when store and added when select. TZ changed - output changed too. – Akina Aug 8 '18 at 13:30
  • But while Passing the data I sent the IST time, Like datetime columns value. – Bhuvanesh Aug 8 '18 at 13:33
  • And? t1 is timestamp, it is recalculated to and from UTC. t2 is datetime, it is stored and selected as is, without recalculation. – Akina Aug 8 '18 at 13:35
3

This is well-documented behaviour:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

The manual specifically says:

By default, the current time zone for each connection is the server's time[...] If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.

  • 1
    ... But is somebody reading the documentation? – Akina Aug 8 '18 at 13:48
  • The documentation is clear, but I may wrongly understand, So My doubt is, While passing the data I used IST time. 18:50:10 --> 13:50:10, again. While changing the timezone, will it use 18:50:10 + 5.30 ==> 23:50:10 ? – Bhuvanesh Aug 8 '18 at 15:11
  • See SHOW VARIABLES LIKE '%zone%'; for clues of the conversion. – Rick James Aug 23 '18 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.