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:
