MySQL offers three types for date and time, namely DATE, DATETIME, and TIMESTAMP. The DATE type displays values in "YYYY-MM-DD" format, whereas both DATETIME and TIMESTAMP adopt the format "YYYY-MM-DD hh:mm:ss". What is the difference between these two date types? How to select one for certain use cases? This article provides an answer to these questions. Let's get started!

Similarities between DATETIME and TIMESTAMP

The date types DATETIME and TIMESTAMP have two common features.

• Display time in the format "YYYY-MM-DD hh:mm:ss", containing both date and time.

• Be able to include fractional seconds part up to microsecond precision.

Differences between DATETIME and TIMESTAMP

Although both DATETIME and TIMESTAMP are used for values containing both date and time, they are quite different with respect to time range, memory usage, automatic format conversion, and the value of function now().

Time range

Memory usage

Automatic format conversion

MySQL, 5 or later versions, converts TIMESTAMP values from the current time zone to UTC for storage, and converts back from UTC to the current time zone for retrieval. But this setting is not applied to DATETIME or other date types.

Function now()

Due to the fact that TIMESTAMP converts values to UTC time for storage, the value of function now() might not equal the current time. In comparison, without the format conversion, the value of now() using DATETIME type always equals the current time.

Use case scenarios

Keep the differences between DATETIME and TIMESTAMP in mind. Let's take a look at two use cases and find the suitable date type for them.

Use case 1: Compute flight duration

Suppose you are working with plenty of flights which cover many countries in different time zones, and you need to provide your customers with the flight durations. For instance, a flight departs from London at 12:30 PM, 2023-02-10 and arrives in New York at 3:35 PM, 2023-02-10.

If you use TIMESTAMP to store the departure and arrival time, then both values will be converted into UTC time. This way, you can obtain the flight duration easily by abstraction. However, if you are using DATETIME, then you need to convert the time manually, which is error-prone, especially when you are handling a large dataset.

Use case 2: Record modification time

Assume you need to add fields such as the modification time of a record or the time of the latest update to your database. More importantly, you want these recorded time to be consistent no matter whichever time zone you are in. Which date type would you choose? DATETIME is surely the answer to keeping fixed values.

Summary

TIMESTAMP indicates an exact point in time and is better used for use cases when your application is timezone dependent. By contrast, DATETIME is a constant value and takes a specific value, and this value is not affected by timezone changes.

Demo on Terminal

Create a table containing both DATETIME and TIMESTAMP types in a database. Then, insert values into it.

Check the inserted values. Note that these two date types only take values in "YYYY-MM-DD hh:mm:ss" format, but the time part can be omitted. When it is omitted, the time "00:00:00" is added automatically.

Now, let's change the time zone from the previous GMT+1 to GMT+3, and check again the dates on the table. We see that values of DATETIME type remain unchanged, but those of TIMESTAMP types are two hours ahead.

Demo on Openblocks

This demo helps you better understand the differences between DATETIME and TIMESTAMP types.

1. Go to Openblocks homepage and click Get Started.

2. Connect to MySQL following this tutorial.

3. Create a table time_demo containing both DATETIME and TIMESTAMP date types.

create table time_demo (`datetime` datetime, `timestamp` timestamp);

4. Create a query to insert values into the table time_demo.

insert into time_demo values (now(), now()), ('1999-02-25', '1999-02-25'), ('1999-02-25 22:11:00', '1979-02-25 22:11:00');

5. View the data on table time_demo in a Table component.

6. Create a new query to change time zone from the current GMT+1 to GMT+3, using code set time_zone = "+03:00";. Check the data after running this query. Again, the values of DATETIME type remain the same as before the time zone change, two hours behind those of TIMESTAMP type.

Openblocks is an open-source low-code platform, offering 50+ built-in UI components. You can also design custom components, extend its possibility by writing JavaScript everywhere, and share your apps with colleagues or customers.

Give it a try and feel free to reach out to our community on Discord if you encounter any issue or would like to share an idea.