Comparison of datetime and timestamp in MySQL

Same

Show

The timestamp column is displayed in the same format as the datetime column. In other words, the display width is fixed at 19 characters, and the format is Yyyy-mm-dd HH:MM:SS.

Different

Range

datetime Retrieves and displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The supported range is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 'TIMESTAMP value cannot be earlier than 1970 or later than 2037

Stores

TIMESTAMP

1.4 Bytes of storage (time stamp value is stored in 4 bytes)

2. Values are saved in UTC (It stores the number of milliseconds)

3. Time zone conversion, the current time zone is converted when stored, and then converted back to the current time zone.

Datetime

1.8 bytes of storage (8 bytes storage)

2. Actual format storage (Just stores what are you having stored and retrieves the same thing which you have stored.)

3. Time zone Independent (It has nothing to deal with the TIMEZONE and Conversion.)

Example comparison

Now I'm going to make a time zone impact on them.

1. Insert a data into the ' T8 ' Values (now (), now ());

2. Change the client time zone (East Zone 9, Japan time zone).

3. Display the inserted data again, changed, timestamp type of data increased by 1 hours

Mysql> CREATE TABLE T8 (id1 timestamp not null,id2 datetime);
Query OK, 0 rows affected

Mysql> INSERT INTO T8 values (now (), now ());
Query OK, 1 row affected

Mysql> SELECT * from T8;
+---------------------+---------------------+
| ID1 | Id2 |
+---------------------+---------------------+
| 2016-03-31 10:15:56 | 2016-03-31 10:15:56 |
+---------------------+---------------------+
1 row in Set

Mysql> set time_zone= ' +9:00 ';
Query OK, 0 rows affected

Mysql> SELECT * from T8;
+---------------------+---------------------+
| ID1 | Id2 |
+---------------------+---------------------+
| 2016-03-31 11:15:56 | 2016-03-31 10:15:56 |
+---------------------+---------------------+
1 row in Set

Comparison of datetime and timestamp in MySQL