JIYIK CN >

DATETIMEand TIMESTAMPare two different data types that can be used to store values ​​that must contain both a date and a time portion.

In this article, we will understand how it is stored in the database and the memory required for each data type. We will also look at the similarities and differences between the two data types and try to understand with examples.

DATETIMETIMESTAMPSimilarities with

DATETIMEThere are some similarities with TIMESTAMP, as shown below:

• Both store the same type of data, with two parts (date and time).
• Both have the same format when queried.
• The format is the same as that stored in the database ( YYYY-MM-DD hh:mm:ss).
• Both require extra bytes to achieve fractional seconds precision.
• Both can change the data with the current date and time whenever a record is updated.

DATETIMETIMESTAMPThe difference between

DATETIMEand TIMESTAMPhave the following differences:

• DATETIMEand TIMESTAMPrequire 5 bytes and 4 bytes respectively.
• TIMESTAMPAffected by time zone, but DATETIMEremains constant.
• DATETIMETIMESTAMPThe supported ranges for and are '1000-01-01 00:00:00'to '9999-12-31 23:59:59'and '1970-01-01 00:00:01'UTCto '2038-01-19 03:14:07' UTC.
• DATETIMEcannot be indexed, but TIMESTAMPcan.
• TIMESTAMPQueries with will be cached, but DATETIMEthis is not the case.

Learn about examples of using DATETIMEand in MySQLTIMESTAMP

Suppose you run a coffee shop in your country. Each customer receives an invoice after paying the bill.

This invoice has, among other details, the date and time. Since you only operate stores in countries where all your customers are in the same time zone, you'll use that DATETIME.

Let's change the scenario a bit; you now have ten coffee shops in different countries, each with its own time zone. Customers will also receive invoices there, but how to display the date and time according to the customer's time zone.

Here you will use TIMESTAMP. Why? Because TIMESTAMPis time zone-sensitive, meaning that TIMESTAMPthe value of is converted from the current time zone (server time) to UTC (universal time zone) for storage and back to the current time zone (server time) for retrieval.

Sample code:

Please run the following code in your MySQL (we are using MySQL 8.0.27 in 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 TIME_STAMPthat the time of the column has changed, but DATE_TIMEthe data of has remained the same.

in conclusion

In this discussion, we conclude that it is better to use DATETIMEand TIMESTAMPdepending on your needs to store the same data. If you want your data to be affected by time zones, go for it TIMESTAMP. Otherwise, it is better to use DATETIME.