DATETIME vs TIMESTAMP in MySQL

1. Similarities of DATETIME and TIMESTAMP
2. Differences of DATETIME and TIMESTAMP
3. Example to Understand the Use of DATETIME and TIMESTAMP in MySQL
4. Conclusion

DATETIME and TIMESTAMP are two different data types that you can use to store a value that must contain the date and time part.

In this article, we will learn about the format in which it is stored in the database and the memory required by each data type. We will also look at the similarities and differences of both data types and try to understand via an example.

Similarities of DATETIME and TIMESTAMP

DATETIME and TIMESTAMP have a few similarities that are listed below:

• Both stores the same type of data, having two parts (date & time).
• Both have the same format when querying them.
• Format for storing in the database is the same (YYYY-MM-DD hh:mm:ss).
• Both need additional bytes for fractional seconds precision.
• Whenever records are updated, both can change data with the current date and time.

Differences of DATETIME and TIMESTAMP

DATETIME and TIMESTAMP have the following differences:

• DATETIME and TIMESTAMP require 5 bytes and 4 bytes, respectively.
• TIMESTAMP is affected by time zone, but DATETIME remains constant.
• Supported range for DATETIME and TIMESTAMP are '1000-01-01 00:00:00' to '9999-12-31 23:59:59' and '1970-01-01 00:00:01'UTC to '2038-01-19 03:14:07' UTC, respectively.
• DATETIME can not be indexed while TIMESTAMP can be.
• Queries with TIMESTAMP will be cached, but it is not the case with DATETIME.

Example to Understand the Use of DATETIME and TIMESTAMP in MySQL

Let’s assume that you are running a coffee shop in your country. Each customer gets an invoice after paying the bill.

This invoice has a date and time in addition to other details. As you are running the shop in your country only where all of your customers will be in the same time zone, you will use DATETIME.

Let’s change this scenario a bit; you have ten coffee shops now in various countries, each country has its time zone. The customers will also get invoices there, but how can you display the date and time according to the customers’ time zone.

Here you will use TIMESTAMP. Why? Because TIMESTAMP is affected by time zone, which means, the value of TIMESTAMP will be converted from the current time zone (server’s time) to UTC (Universal Time Zone) for storage and back to the current time zone (server’s time) on retrieval.

Example Code:

Please run the following code in your MySQL (we are using MySQL 8.0.27 for this tutorial) and see the output given after the code.

#create schema
CREATE SCHEMA db_practice_datetime_timestamp;

#create table
CREATE TABLE practice_datetime_and_timestamp (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    DATE_TIME DATETIME,
    TIME_STAMP TIMESTAMP
    );
#insert data   
INSERT INTO practice_datetime_and_timestamp(TIME_STAMP,DATE_TIME)
VALUES(NOW(),NOW());

#read data
SELECT DATE_TIME, TIME_STAMP from practice_datetime_and_timestamp;

Output:

Now, run the code given below and see its output.

SET time_zone = '-05:00';
SELECT DATE_TIME, TIME_STAMP from practice_datetime_and_timestamp;

Output:

You may have noticed that the time has changed for the TIME_STAMP column, but the data for DATE_TIME remains the same.

Conclusion

In this discussion, we have concluded that DATETIME and TIMESTAMP store the same data according to your needs. If you want your data affected by time zone, go for TIMESTAMP. Otherwise, it is good to use DATETIME.