MySQL TIMESTAMP date type changes the values with the time-zone while DATETIME is unchanged.

By 
mysql> show variables like '%time_zone%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| system_time_zone | UTC   |
| time_zone        | UTC   |
+------------------+-------+

mysql> create table datedemo(mydatetime datetime,mytimestamp timestamp);

mysql> insert into datedemo values ((now()),(now()));
Query OK, 1 row affected (0.00 sec)

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2020-12-22 20:08:53 | 2020-12-22 20:08:53 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone="america/new_york";
Query OK, 0 rows affected (0.01 sec)

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2020-12-22 20:08:53 | 2020-12-22 15:08:53 |
+---------------------+---------------------+
1 row in set (0.00 sec)
In MySQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

Comments

Post a Comment