In this blog, I’ll write about the differences between the TIMESTAMP and DATETIME types in MySQL. Most of the time you might not notice any difference using any of these but there’s one important difference that you should know about them.
So first, a timestamp is used to represent a point in time. Depending on your location, this point in time does not happen at the same time for everyone in the world. Since we need to define one point in time somewhere in the world, MySQL translates timestamp values from your timezone setting to UTC. The value you get when you fetch timestamp values from the database, therefore, changes depending on your timezone setting.
In contrast, a DATETIME type doesn’t change depending on the timezone, it’s like a date on a calendar. A point in time can be different from where you live but a date in a calendar is always the same where ever you are located in the world.
As said, timestamp values are based in UTC, which means that if your database or current database session is also set to UTC, then MySQL doesn’t have to do any date or time conversions. The values you insert and fetch from the database would therefore be identical.
However, if you’ve set your timezone setting to another timezone than UTC, then MySQL will translate whatever value you insert to UTC by reducing or adding hours to the value.
Inserting and Fetching Values
So now that we’ve covered the basics, let’s look at some examples of how this works in more detail. So, let’s start with this database table, where we insert values in the europe/paris timezone. This could be that the database is set to this timezone or that your current database session is set to this timezone.
set time_zone="europe/paris";
create table demo(datetime datetime, timestamp timestamp);
insert into demo values ((now()), (now()));
insert into demo values ('2022-12-06 15:00:00', '2022-12-06 15:00:00');Now let’s try to fetch these values in different timezones.
// Same timezone so values are the same as the inserted ones.
set time_zone="europe/paris";
select * from demo;
| datetime | timestamp |
|----------------------|----------------------|
| 2022-12-06T07:06:33Z | 2022-12-06T07:06:33Z |
| 2022-12-06T15:00:00Z | 2022-12-06T15:00:00Z |
| | |
// Values in the timestamp column are shifted one hour back.
set time_zone="UTC";
select * from demo;
| datetime | timestamp |
|----------------------|----------------------|
| 2022-12-06T07:06:33Z | 2022-12-06T06:06:33Z |
| 2022-12-06T15:00:00Z | 2022-12-06T14:00:00Z |
| | |
// Values in the timestamp column are shifted forward by 8 hours.
set time_zone="asia/tokyo";
select * from demo;
| datetime | timestamp |
|----------------------|----------------------|
| 2022-12-06T07:06:33Z | 2022-12-06T15:06:33Z |
| 2022-12-06T15:00:00Z | 2022-12-06T23:00:00Z |
| | |As you can see in the result above, both now() and the date time string ‘2022-12-06 15:00:00’ got translated in the timestamp column while the datetime column didn’t get affected by any of the timezone setting changes.
So, if you’re 100% sure that you’re always using the same timezone as system_time_zone and time_zone in your MySQL database and you’re always using that same time_zone in your MySQL client when fetching data from your database, then you would not notice any difference between TIMESTAMP and DATETIME.
However, if you’re not careful then this automatic conversion of timestamp values when inserting and fetching values could potentially work against you, storing and retrieving wrong values in your database.
When to use the TIMESTAMP type?
Let’s say you have a database server accumulating data for analytics, support tickets or incidents reports, whatever really. You are a data analyst sitting in asia/tokyo timezone like me, while your colleague sits in the america/new_york timezone.
If you’ve set up your database tables to use TIMESTAMP columns, you can simply switch to your timezone in the session of your database connection and you’ll get all the timestamps translated to your timezone.
To fetch all entries from the day before, you could simply do a query as you’d normally do without thinking about timezone conversions. Super handy!
set time_zone="asia/tokyo";
select * from demo where timestamp between '2022-12-05 00:00:00' and '2022-12-05 23:59:59'However, there are some things to consider when using the TIMESTAMP column. Without getting too deep into the technical specifications, it’s worth noting that TIMESTAMP will return 0 after 03:14:07 UTC on 19 January 2038.
When to use the DATETTIME type?
In a typical web application using a database server, I think there aren’t any real advantages using TIMESTAMP. There are mainly two reasons for this. One is the fact that it will stop working in January 2038, and another one is the potential risk of wrong values if the timezone settings are not consistent.
An application should always work with dates and times in UTC in the infrastructure and domain layers. Since it’s difficult for us humans to understand UTC timezone (if we’re not based in it) we’d need to do some translation for the end-user to make it more user-friendly.
These types of timezone conversions for your users should always take place in the topmost part of your application, just before displaying the value to your user. The same goes for if the user posts date or time data back to your application, it’s important to immediately do the conversion back to UTC in the top layer of your application before passing it on to your domain and infrastructure logic.
When to use which?
- Using DATETIME, you're safeguarding yourself against any timezone setting changes that might happen outside your control.
- Since the TIMESTAMP type will hit its limit in 2038, it's more future-proof to use the DATETIME type.
- In a scenario where your database and all the connection sessions are consistently set to UTC, you will not notice much of a difference between these columns.
That’s it for this time, I hope that this will be useful for someone out there wondering about the differences between these two column types in MySQL.
Until next time, have a good one!