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.
Range
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.
Conversion
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.
Summary
If you want higher range, use DATETIME and if your application is timezone independent, you should use TIMESTAMP.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Nicely documented.. Thanks Pinal
So always use DATETIME, right? ;)
Thank you. It is clear now.
Still not clear lol.
Not properly documented. You confused a lot.
Good info, but please double check your Summary – “If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.” Shouldn’t the last one be “TIMESTAMP”? And can you clarify WHY you would use DATETIME when you are changing timezones – I would have thought you wanted TIMESTAMP there.
Clear explanation. Thanks a lot.
Thanks vikash.
“If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.”
You should fix that typo…
“use DATETIME and if your application is timezone independent” …. is correct, “use TIMESTAMP if your application is timezone dependant” or am I wrong ?
It’s been corrected. You should use TIMESTAMP for timezone-independent times (such as when a comment was posted, or when an order was submitted). DATETIME is only suitable for timezone-dependent times (such as the time of a physical event in local time); it cannot uniquely represent times during the hour before or after Daylight Saving Time ends.
What if I want to store an absolute point in time, but not suffer from the year 2038 problem?
If this is correct what Jake said then use of DATETIME shuld work with no problems as the range is crazy wider than TIMESTAMP.