Welcome to Learn Tech Tips Blog, Many friend have a questions and wonder “Whether to assign or TIMESTAMP datatype for a column as both seem to store the same data?”. Even though they store the same data, so today question and answer will solved it

Should I use the datetime or timestamp data type in MySQL?

 Compare DATATIME and  TIMESTAMP

Similarities things

+ Both store data in “YYYY-MM-DD HH:MM:SS” format

+ Both can store data as well as time part

Difference things

DATETIME

+ Support range is ‘1000-01-01 00:00:00’ to  ‘9999-12-31 23:59:59’

+ Prior to MySQL 5.6.4, DATETIME requires 8 bytes

+ In MySQL 5+. DATETIME does not do any conversion

+ DATETIME data can not be indexed

+ DATETIME is used mostly for user-data

+ DATETIME is constant does not affected by different TIME ZONE

 TIMESTAMP

+ Support range is ‘1970-01-01 00:00:00’ to  ‘2038-01-09 03:14:07’ UTC

+ Prior to MySQL 5.6.4, TIMESTAMP requires 4 bytes

+ In MySQL 5+. TIMESTAMP value convert from current time to UTC and vice-versa

+ TIMESTAMP data can be indexed

+ TIMESTAMP is used mostly for metadata ex: row created/modified

+ TIMESTAMP affected by different TIMEZONE related setting

Any feedback or question, leave your comment, we can discuss about it!

Learn Tech Tips – Zidane 

Reference: