Difference between datetime and timestamp in MySQL

In this post, I am sharing the difference between DATETIME and TIMESTAMP data type of MySQL.

I am writing about this topic because this is very necessary and important for Database Developers.
Even many interviewers also ask this question in the MySQL interview.

For this topic, You can find many alternative answers over the internet. But still, I love to write in my way.

DATETIME vs TIMESTAMP:

TIMESTAMP used to track changes of records, and update every time when the record is changed.
DATETIME used to store specific and static value which is not affected by any changes in records.

TIMESTAMP also affected by different TIME ZONE related setting.
DATETIME is constant.

TIMESTAMP internally converted a current time zone to UTC for storage, and during retrieval convert the back to the current time zone.
DATETIME can not do this.

TIMESTAMP is 4 bytes and DATETIME is 8 bytes.

TIMESTAMP supported range:
‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC
DATETIME supported range:
‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′

Let me demonstrate a small practical example:

Create test table and insert NOW() in the both column:

1 2 3 4 5 6 7 8 9

CREATE TABLE tbl_TestDateType (     ID INTEGER     ,Date1 DATETIME     ,Date2 TIMESTAMP );   INSERT INTO tbl_TestDateType VALUES (1,NOW(),NOW());

Update a record after a few seconds:

1	UPDATE tbl_TestDateType SET ID=2;

Check the value for both date, You will find old value in DATETIME column and updated TIMESTAMP column.

1	SELECT * FROM tbl_TestDateType;