This is a one of the most popular question, I often receive when MySQL Developers are creating a database. There are multiple datatypes which can store DateTime datatype in MySQL. The usual confusion comes up between DATETIME and TIMESTAMP.
DATETIME and TIMESTAMP – both of them can store datetime data just fine and retrieve them back, hence the question is which one to use and why?
Here are two major factor which can help you to decide which one of them, you should use for your database design.
The supported range for DATETIME type is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
The supported range for TIMESTAMP type is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
That means if you want to store date which is before the year 1970 or after the year 2038 you will need to use DATETIME.
As per the MySQL official documentation – MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
This means, if your application is such where you want time to stay absolutely steady with respect to GMT, you must use TIMESTAMP, or else you should use it with DATETIME.
For example, if I am using using forum, I will use TIMESTAMP as I want to capture the time when user have left comments, but if I am using an application where I have to deliver goods as per local time (and my timezone is changing), I will use DATETIME.
If you want higher range, use DATETIME and if your application is timezone independent, you should use TIMESTAMP.
Reference: Pinal Dave (http://blog.sqlauthority.com)