This post's content
DATETIME and TIMESTAMP
Some MySQL data types are a bit confusing at first sight. Without reading through the documentation (and sometimes even after reading it), it’s hard to figure out which data type is the right one for your current needs. That’s why we’re here.
So you need to store date and time pieces of information, but wondering which data type you should use in MySQL - DATETIME or TIMESTAMP? The answer is - it depends on your needs.
First, let’s look into the MySQL official documentation to see what MySQL has to say about both of them and then we’ll look into practical uses for each of them.
DATETIME - “The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.”
TIMESTAMP - “The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.”
How are they different?
Well, I’m glad I read the documentation mentioned abvove, because they look, hmmm, the same. But, if you keep reading that documentation page, you’ll eventually reach this part:
“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 does not occur for other types such as DATETIME.)”.
So how does this impact your implementation and whether to choose DATETIME or TIMESTAMP as your data type?
Well, as mentioned above, when using the TIMESTAMP data type, the values are converted by the database to UTC (universal time zone) and are stored in that timezone. This means that when you fetch (SELECT) this data, a conversion will be done from UTC to the current time zone, and only then the data will be returned. This behavior doesn’t occur for DATETIME stored values.
Practical uses
Well, if you are serving customers in different countries with different application instances, by using TIMESTAMP, you’ll be able to serve the same date and time data in different timezones, directly from the database.
Please note that by default the applied timezone is the server’s timezone. You can set the timezone on a per connection basis if you wish to change it. For example, SET time_zone = '-8:00'; .
To summarize, if you want to serve your date and time data the same way regardless of timezones, you can use the DATETIME type (which will also allow you to use all the date & type functions built in MySQL). Otherwise, you can use TIMESTAMP and serve the data on a per-timezone basis.
