In this post, I demonstrate difference between DATETIME and TIMESTAMP data type of MySQL.
I am writing about this topic because this is very basic and important for Database Developers.
Even many interviewers also ask this question to MySQL Database Developer.
For this topic, You can find many alternative answers over the internet. But still I love to write in my own way.
DATETIME vs TIMESTAMP:
TIMESTAMP used to track changes to 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 current time zone to UTC for storage, and during retrieval converted 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() for both column:
CREATE TABLE tbl_TestDateType
INSERT INTO tbl_TestDateType
Update this record after a few times.
UPDATE tbl_TestDateType SET ID=2;
Check the value for both date, You will find old value in DATETIME column and updated TIMESTAMP column.
SELECT * FROM tbl_TestDateType;