Skip to content
geekerhub

Using the DATETIME and TIMESTAMP Data Types in MySQL

By ali 5 min read

MySQL DATETIME and TIMESTAMP data types with SQL examples

MySQL provides two commonly used data types for storing date and time values: DATETIME and TIMESTAMP. Both can store a date and time together, but they behave differently when MySQL handles time zones and automatic date updates.

Choosing the right data type depends on how your application uses dates. For example, you might use DATETIME to store an event’s scheduled start time or TIMESTAMP to record when a database row was created or modified.

In this guide, you will learn how to create columns with these data types, insert date and time values, and use MySQL functions to work with them.

1. Using the DATETIME Data Type in MySQL

The MySQL DATETIME data type stores a date and time combination in the following format:

YYYY-MM-DD HH:MM:SS

The format represents:

  • YYYY: Year
  • MM: Month
  • DD: Day
  • HH: Hour in 24-hour format
  • MM: Minute
  • SS: Second

For example, the following value represents January 1, 2022, at 12:30 PM:

'2022-01-01 12:30:00'

You can create a table with a DATETIME column using this SQL statement:

CREATE TABLE events (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_time DATETIME NOT NULL
);

The start_time column stores the date and time associated with each event.

2. Using the TIMESTAMP Data Type in MySQL

The MySQL TIMESTAMP data type also stores date and time values. However, it handles time zones differently from DATETIME.

MySQL converts TIMESTAMP values from the current session time zone to UTC when storing them and converts them back to the session time zone when retrieving them. By comparison, DATETIME values are not automatically converted based on the session time zone.

You can create a table with a TIMESTAMP column like this:

CREATE TABLE events (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

In this example, DEFAULT CURRENT_TIMESTAMP tells MySQL to use the current date and time when a new record is inserted without a value for start_time.

3. Automatically Update a TIMESTAMP Column

You can also configure a TIMESTAMP column to update automatically whenever the row changes. To do this, use ON UPDATE CURRENT_TIMESTAMP.

CREATE TABLE events (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

In this example:

  • created_at stores the time when the record is created.
  • updated_at is initialized when the record is created and automatically updated when the row changes.

Remember that automatic initialization and updating are controlled by column definitions. A TIMESTAMP column does not automatically update in every situation unless you configure it to do so.

4. Insert the Current Date and Time

MySQL provides the NOW() function to return the current date and time. You can use it when inserting a record into a table.

INSERT INTO events (name, start_time)
VALUES ('Product Launch', NOW());

This inserts the current date and time into the start_time column, whether it uses DATETIME or TIMESTAMP.

You can also insert a specific date and time by providing a value in the appropriate format:

INSERT INTO events (name, start_time)
VALUES ('Annual Meeting', '2026-12-15 10:30:00');

5. Extract Date and Time Values in MySQL

MySQL provides several functions that allow you to extract specific parts of a DATETIME or TIMESTAMP value.

Get the Date Using DATE()

The DATE() function returns only the date portion of a date and time value.

SELECT DATE(start_time) AS event_date
FROM events;

Get the Time Using TIME()

The TIME() function returns the time portion of a date and time value.

SELECT TIME(start_time) AS event_time
FROM events;

Get the Year Using YEAR()

The YEAR() function extracts the year from a date or date and time value.

SELECT YEAR(start_time) AS event_year
FROM events;

6. Other Useful MySQL Date and Time Functions

In addition to extracting date and time parts, MySQL provides functions for adding, subtracting, and formatting date values.

DATE_ADD()

Use DATE_ADD() to add a time interval to a date or date and time value.

SELECT DATE_ADD(
    '2026-01-01 12:00:00',
    INTERVAL 7 DAY
) AS new_date;

This example adds seven days to the supplied date and time.

DATE_SUB()

Use DATE_SUB() to subtract a time interval.

SELECT DATE_SUB(
    '2026-01-01 12:00:00',
    INTERVAL 7 DAY
) AS previous_date;

This example subtracts seven days from the supplied value.

DATE_FORMAT()

The DATE_FORMAT() function lets you display a date or date and time value in a custom format.

SELECT DATE_FORMAT(
    start_time,
    '%d-%m-%Y %h:%i %p'
) AS formatted_date
FROM events;

This formats the value using a day-month-year layout followed by the time in 12-hour format.

7. Difference Between DATETIME and TIMESTAMP

Although both data types store date and time values, there are some important differences to consider when designing your database.

  • Time zone handling: MySQL converts TIMESTAMP values between the session time zone and UTC. DATETIME values are not automatically converted.
  • Automatic values: Both data types can use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP when configured in the column definition.
  • Value range: DATETIME supports a much wider range of dates, while TIMESTAMP has a more limited range. In MySQL 8.0, the standard TIMESTAMP range ends in January 2038.
  • Typical use: DATETIME is useful for dates and times that should remain as entered, while TIMESTAMP is often useful for recording events such as row creation and modification.

For example, an event scheduled to begin at a particular local time may be stored as DATETIME. A record creation time that should be handled consistently across different session time zones may be better suited to TIMESTAMP.

For additional details and supported formats, refer to the official MySQL date and time data type documentation.

Conclusion

MySQL provides both DATETIME and TIMESTAMP for storing date and time values. The main difference is how they handle time zones and the range of values they support.

You can use functions such as NOW(), DATE(), TIME(), YEAR(), DATE_ADD(), DATE_SUB(), and DATE_FORMAT() to insert, extract, calculate, and format date and time values.

Choose the data type based on how your application needs to store and display dates, especially when users work across different time zones.

Related guides