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.
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 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.
Top comments (2)
So should we go with datetime or timestamp column in all mysql tables?
Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.