What is the difference between MySQL DATETIME and TIMESTAMP data type? (2024)

Both the data types store data in “YYYY-MM-DD HH:MM:SS” format and include date as well as time. In spite of these similarities they are having the following differences −

• Range − Datetime data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. But timestamp data type supports a date along with time in the range between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’.
• Size − Datetime requires 5 bytes along with 3 additional bytes for fractional seconds’ data storing. On the other hand, timestamp datatype requires 4 bytes along with 3 additional bytes for fractional seconds’ data storing. But before MySQL 5.6.4, DateTime requires 8 bytes along with 3 additional bytes for fractional seconds’ data storing.
• Conversion from one timezone to other − Actually in MySQL5+, timestamp value converts from current time to UTC and vice-versa while datetime does not do any conversion.
• Indexing − Indexing can be done on timestamp data but datetime data cannot be indexed.
• Caching of query − queries having timestamp datatype can be cached but queries having datetime datatype cannot be cached.

Above were some major differences between DATETIME and TIMESTAMP datatype and 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 timezone to UTC-05:00 and the result is changed for table having TIMESTAMP datatype.

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)