What is the difference between DATETIME and TIMESTAMP data type in MySQL?

What is the difference between DATETIME and TIMESTAMP data type in MySQL?

Written by Bobby Iliev on Dec 4th, 2021 ・ Views ・ Report Post

Bobby Iliev Visit User Profile

Introduction

If you have ever worked with MySQL and had to create your table structure, you have probably noticed that the DATETIME and TIMESTAMP data types are quite similar at a first glance as they have a lot of similarities.

In this tutorial, we will go over each one of them and learn the main differences between both so that you could know when you should use them.

https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

DATETIME

The DATETIME type is used for values that contain both the date and the time.

For example, when retrieving the value, MySQL will display it in the following format: YYYY-MM-DD hh:mm:ss.

An important distinction is that the range that is currently supported is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

You can think of DATETIME as a literal value, as it stores the date and the time but has no reference to a specific timezone. So when you store the value of NOW(), for example, the server time zone will be used. Once inserted, the value of the DATETIME will remain the same regardless of any current sessions.

This is an important distinction that will get a bit clearer once you go over the example below.

TIMESTAMP

Just as DATETIME, the TIMESTAMP data type contains both the date and the time in the following format YYYY-MM-DD hh:mm:ss.

🤩 Our Amazing Sponsors 👇
DigitalOcean View Website
DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.
Laravel News View Website
Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.
Genesis View Website
A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components.

However, unlike DATETIME, the TIMESTAMP data type has a fixed range between 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. You should always consider this limit as it might not be suitable for all applications and use cases. For more information on why this specific range, make sure to read more about the Unix Time.

An important thing to keep in mind is that when you display the values of a TIMESTAMP column, they would be converted to the current time zone. What happens is that MySQL would convert the TIMESTAMP values from the current time zone to UTC when storing them, but then it would convert the values back from UTC to the current time zone of the retrieval.

Let's go through the following example to make this a bit more clear!

Example

Let's create a very simple table with just two columns for each of the two data types:

CREATE TABLE demo_table(
    datetime_column DATETIME,
    timestamp_column TIMESTAMP
);

Then let's insert the same values in both using NOW():

INSERT INTO demo_table VALUES ((NOW()),(NOW()));

Then check the values of both columns:

SELECT * FROM demo_table;

// Output
+---------------------+---------------------+
| datetime_column     | timestamp_column    |
+---------------------+---------------------+
| 2021-12-04 17:42:16 | 2021-12-04 17:42:16 |
+---------------------+---------------------+

So far the values of both columns are the same. But then let's check the current time zone:

SHOW VARIABLES LIKE '%time_zone%';

// Output
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+

Next, let's change the time zone for our current session:

SET time_zone="America/New_york";

Note: if you get the following error: ERROR 1298 (HY000): Unknown or incorrect time zone: 'America/new_york' you can run the following command in another terminal window:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

After that let's see how the output values change:

SELECT * FROM demo_table;

// Output
+---------------------+---------------------+
| datetime_column     | timestamp_column    |
+---------------------+---------------------+
| 2021-12-04 17:42:16 | 2021-12-04 12:42:16 |
+---------------------+---------------------+

As you can see, the displayed value of the datetime_column remained the same but the timestamp_column value changed and is now referencing our current time zone.

Conclusion

This is all! Now you should have a better idea of what the main differences between datetime and timestamp are!

In case that you want to learn more about SQL in general, I would recommend the following free eBook:

In case that you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out Materialize!

Materialize - a streaming database

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.

Comments (0)