Datetimes versus timestamps in MySQL
By Aaron Francis |
There are several different ways to store dates and times in MySQL, and knowing which one to use requires understanding what you'll be storing and how MySQL handles each type.
There are five column types that you can use to store temporal data in MySQL. They are:
DATEDATETIMETIMESTAMPYEARTIME
Each column type stores slightly different data, has different minimum and maximum values, and requires different amounts of storage.
In the table below, you'll see each column type and their various attributes.
| Column | Data | Bytes | Min | Max | |-----------|-------------|-------|---------------------|---------------------| | DATE | Date only | 3 | 1000-01-01 | 9999-12-31 | | DATETIME | Date + time | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | | TIMESTAMP | Date + time | 4 | 1970-01-01 00:00:00 | 2038-01-19 03:14:17 | | YEAR | Year only | 1 | 1901 | 2155 | | TIME | Time only | 3 | -838:59:59 | 838:59:59 |
Dates, years, and times
Based on this table, the first question you must ask yourself is: "What kind of data do I need to store?"
The DATE column type
If you are storing a date only, with no time information, the choice is easy! You would use the DATE column. This column is helpful for things like birthdays, anniversaries, employee start dates, etc. The DATE column is a no-fuss affair: it stores dates.
It can store a massive range, from the beginning of the year 1000 to the end of the year 9999, in a minimal footprint, only 3 bytes.
The YEAR column type
Likewise, if you're storing a year with no date information, there is a unique column just for that: the YEAR column. This column is not widely used, but it serves its purpose exceedingly well when you need it. The YEAR column is compact at only 1 byte, but the range is not very large, from 1901 to 2155. If you need a wider range, you'd probably migrate to a signed or unsigned small integer, depending on your needs.
The TIME column type
Finally, rounding out the single-purpose column types is the TIME column. The time column stores time only, in the hhh:mm:ss format. The legal range of this column is much wider than 00:00:00 to 23:59:59 because you can use it to store intervals of time and wall-clock time. The valid range spans from -838:59:59 to 838:59:59, approximately 35 days in either direction.
If you are storing a time or duration irrespective of date, the TIME column is made for that. If you are storing a date and time together, you should use either the DATETIME or TIMESTAMP columns to keep it as one logical unit instead of splitting it into two columns.
Legal ranges of TIMESTAMP and DATETIME in MySQL
DATETIMEs and TIMESTAMPs both serve a similar purpose: storing a reference to a specific point in time. If you were tasked with storing a reference to February 14th, 2029, at 8:47am, you could use either type to satisfy that requirement!
Again, the question of whether to use a DATETIME or TIMESTAMP in MySQL depends on what you are trying to do. Neither is inherently bad, and neither is inherently good.
The first difference is that the storage size for a TIMESTAMP is 4 bytes, which is half the size of a DATETIME at 8 bytes. Storage space is cheap, but when designing table schemas, we always want to choose the smallest possible column type that fits the full range of our data. When creating your tables, there is no need to be generous!
Because the storage size of the TIMESTAMP is much smaller than the DATETIME, the range of legal values is likewise much smaller.
The 2038 problem in MySQL
A TIMESTAMP ranges from 1970-01-01 00:00:00 to 2038-01-19 03:14:17. The year 2038 might sound familiar to you! This is one incarnation of the famous Year 2038 Problem. You may have also seen it written as Y2038, Y2K38, or even The Epochalypse, which should be credited for its clever wordplay.
The 2038 issue arises in systems that calculate Unix time, defined as the seconds that have passed since the Unix epoch at 00:00:00 UTC on January 1, 1970. These systems use a signed 32-bit integer to store this time, which can only hold integer values between -2^31 and 2^31 - 1. The maximum timestamp that can be accurately represented corresponds to 2^31 - 1 seconds after the epoch, which is 03:14:07 UTC on January 19, 2038.
For our purposes, this immediately eliminates TIMESTAMP as a contender for storing dates in the far future. If there is even a reasonable chance that you might need to store dates beyond 2038, it's best to use a DATETIME.
For now, there are instances where a TIMESTAMP is perfectly adequate! One such scenario is when you are recording the current time when an action takes place. Because we're still more than a decade away from the year 2038, it's a perfectly reasonable choice to use TIMESTAMP for columns like:
updated_atcreated_atdeleted_atarchived_atposted_at
These columns are usually populated by your application. As long as these columns record the current time at which an event happened, you don't have to worry about the 2038 problem for a long time. And surely, by then, we'll have a solution for it. (Surely, right?)
When to use DATETIME
Given the relatively narrow range of legal values for a TIMESTAMP column, you should consider a DATETIME for any datetime where you might need to populate a date outside the 1970 to 2038 range. If the field is open to user input, you'll likely want to use a DATETIME or validate that the input falls within the 1970-2038 window for a TIMESTAMP.
Timezone treatment of DATETIME versus TIMESTAMP in MySQL
The storage size and the legal range are the most significant, most obvious differences between the DATETIME and TIMESTAMP columns, but there is one slightly more pernicious difference: timezones. Timezones are the bane of almost every developer's existence, and you're not free from them in MySQL either.
The DATETIME column does absolutely nothing concerning timezones. If you write a value of 2029-02-14 08:47 into the database, you will always and forever receive a value of 2029-02-14 08:47. No matter what your server or connection timezones are set to, you'll always get 2029-02-14 08:47 back.
TIMESTAMP on the other hand, tries to "help you out" by always converting to and from UTC. From the 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 does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.
Let's do a small example to prove this. We will create a table with DATETIME and TIMESTAMP columns and insert some data.
CREATE TABLE timezone_test (
`timestamp` TIMESTAMP,
`datetime` DATETIME
);
Now we'll explicitly set our connection timezone to UTC and insert 2029-02-14 08:47:
SET SESSION time_zone = '+00:00';
INSERT INTO timezone_test VALUES ('2029-02-14 08:47', '2029-02-14 08:47');
SELECT * FROM timezone_test;
-- | timestamp | datetime |
-- |---------------------|---------------------|
-- | 2029-02-14 08:47:00 | 2029-02-14 08:47:00 |
Looking good so far; the value we inserted is the value we got back! Let's change our session timezone to -05:00 now and see what happens:
SET SESSION time_zone = '-05:00'; SELECT * FROM timezone_test; -- | timestamp | datetime | -- |---------------------|---------------------| -- | 2029-02-14 03:47:00 | 2029-02-14 08:47:00 |
You'll notice that the timestamp column has shifted five hours, while the datetime column remains the same. In practice, this shouldn't matter too much, as hopefully your server and connection timezones are always set to UTC, but if they aren't, you might be in for a big surprise when the values you put in the database are not the values you get back out.
Hopefully this helps demystify the differences between all of the columns you can use to store temporal data in MySQL! If you'd like to watch a video on the same topic, you can find that and much more in our free MySQL for Developers course.