Difference between datetime and timestamp in MySQL

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.


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:

Update this record after a few times.

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

MySQL DateTime Timestamp

Anvesh Patel

Leave a Reply

3 Comments on "Difference between datetime and timestamp in MySQL"

Notify of
Sort by:   newest | oldest | most voted

Nice post !. Guys I am telling you, Anvesh is one of the next big person in Database Technology. He has just started blog and very popular in couple of months. Anvesh I can see your future after 3 to 4 year, you will be next to Pinal Dave(SQL Server Specialist).
I am also Senior Database Architecture.
Thanks for the help !

TIMESTAMP used to track changes to records, and update every time when the record is changed, ‘timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP’ make it; select version(); +————+ | version() | +————+ | 5.6.28-log | +————+ 09:46:02 scott> create table t1 (id int,dt datetime,ts timestamp); Query OK, 0 rows affected (0.01 sec) 09:46:31 scott> insert into t1 values (1,now(),now()); Query OK, 1 row affected (0.01 sec) 09:46:47 scott> select * from t1; +——+———————+———————+ | id | dt | ts | +——+———————+———————+ | 1 | 2016-05-05 09:46:47 | 2016-05-05 09:46:47 | +——+———————+———————+ 1 row in set (0.00 sec) 09:46:54 scott>… Read more »