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

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

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

Notify of

Sort by:   newest | oldest | most voted
11 months 14 hours ago

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 !

5 months 22 days ago
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 »