What is the difference between the MySQL DATETIME and TIMESTAMP data types?

Both data types store data in the format "YYYY-MM-DD HH:MM:SS" and include date and time. Despite these similarities, they have the following differences -

• Range- The datetime data type supports dates and times between 1000 - 01-01 00:00 :00 and 9999-12-31 23:59:59. However, the timestamp data type supports dates and times between "1970-01-01 00:00:01" and "2038-01-19 08:44:07".
• Size- Datetime requires 5 bytes plus 3 additional bytes for fractional seconds data storage. On the other hand, the timestamp data type requires 4 bytes plus 3 additional bytes for fractional seconds data storage. But prior to MySQL 5.6.4, DateTime required 8 bytes plus 3 additional bytes for fractional seconds data storage.
• Conversion from one time zone to another- Actually in MySQL5, timestamp values are converted from current time to UTC and vice versa, while datetime does not undergo any conversion.
• Indexing- Timestamp data can be indexed, but datetime data cannot.
• Indexing- Timestamp data can be indexed, but datetime data cannot. li>
• Query Cache- Queries of timestamp data type can be cached, but queries of date and time data type cannot be cached.

Above are some of the major differences between DATETIME and TIMESTAMP data types, the following example will demonstrate it -

Example

mysql> Create table test_datetime(time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.44 sec) mysql> INSERT INTO test_datetime (time) values (CURRENT_TIMESTAMP); Query OK, 1 row affected (0.04 sec) mysql> Select * from test_datetime; +---------------------+ | time | +---------------------+ | 2017-11-14 17:29:03 | +---------------------+ 1 row in set (0.00 sec) mysql> Create table test_timestamp(time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.64 sec) mysql> INSERT INTO test_timestamp (time) values (CURRENT_TIMESTAMP); Query OK, 1 row affected (0.06 sec) mysql> Select * from test_timestamp; +---------------------+ | time | +---------------------+ | 2017-11-14 17:29:50 | +---------------------+ 1 row in set (0.00 sec)

Now, in the following query, We have changed the time zone to UTC-05:00 and the results for tables with TIMESTAMP data type have also changed.

mysql> SET @@session.time_zone = '-5:00'; Query OK, 0 rows affected (0.00 sec) mysql> Select * from test_timestamp; +---------------------+ | time | +---------------------+ | 2017-11-14 06:59:50 | +---------------------+ 1 row in set (0.00 sec)

Output

mysql> Select * from test_datetime; +---------------------+ | time | +---------------------+ | 2017-11-14 17:29:03 | +---------------------+ 1 row in set (0.00 sec)

The above is the detailed content of What is the difference between the MySQL DATETIME and TIMESTAMP data types?. For more information, please follow other related articles on the PHP Chinese website!