Working with Dates in MySQL

28 Oct 2026

Working with dates and times can become complicated quickly. Months have different lengths, leap years add exceptions, time zones can change how a value is interpreted, and even a seemingly simple query can affect database performance.

MySQL provides dedicated date and time data types along with a wide range of functions for calculations, formatting, comparisons, and time-zone conversion. Once you understand the main data types and a few reliable query patterns, working with dates becomes much easier.

This guide covers the most useful concepts for working with dates and times in modern MySQL, including DATE vs DATETIME vs TIMESTAMP, date arithmetic, efficient date-range queries, and some date-related considerations that are useful when testing applications.

Date and Time Data Types

MySQL provides five main temporal data types:

DATE — Stores a date without a time component, using the YYYY-MM-DD format. It is appropriate when the time of day isn't relevant, such as a birth date or a business due date.
TIME — Stores a time or elapsed-time value using a format such as HH:MM:SS. Unlike a typical time-of-day type, MySQL TIME can also represent durations greater than 24 hours.
DATETIME — Stores both a date and a time, such as 2026-09-21 14:30:00. It does not perform automatic time-zone conversion.
TIMESTAMP — Stores a date and time and performs conversion between the session time zone and UTC when values are stored and retrieved. This makes it useful when representing a specific point in time.
YEAR — Stores a year value. Modern applications should generally use the four-digit representation rather than the older two-digit format.

DATETIME vs TIMESTAMP

The distinction between DATETIME and TIMESTAMP is particularly important.

With DATETIME, MySQL stores the date and time value without automatically converting it between time zones.

With TIMESTAMP, MySQL converts the value between the session time zone and UTC when storing and retrieving it.

For example, suppose an application stores:

2026-09-21 12:00:00

in a TIMESTAMP column. The actual UTC instant represented by that value depends on the session time zone in effect when the value is written. When another session retrieves the value using a different time zone, MySQL can display the corresponding local time.

This makes TIMESTAMP useful for values such as:

API request timestamps
Login events
Transaction timestamps
Audit records
Other events representing a specific instant in time

DATETIME can be more appropriate when the stored value represents a date and time that should remain exactly as entered, independent of automatic time-zone conversion.

The choice matters because changing a temporal column's type later can be more complicated than choosing the appropriate type when designing the database.

Fractional Seconds

Modern MySQL also supports fractional seconds for TIME, DATETIME, and TIMESTAMP.

For example:

created_at DATETIME(3)

can store milliseconds, while:

created_at DATETIME(6)

can store microseconds.

This can be useful for applications that need to distinguish events occurring very close together, such as high-volume API requests or detailed event logging.

Querying Dates and Times

Because MySQL understands temporal values natively, date comparisons can be performed directly in SQL.

For example, suppose an application stores API requests in a table called api_requests with a created_at column of type DATETIME.

To retrieve requests created during a specific period:

SELECT *
FROM api_requests
WHERE created_at >= '2026-09-21 08:00:00'
  AND created_at < '2026-09-21 18:00:00'
ORDER BY created_at ASC;

The same approach can be used when filtering by an entire day.

A common pattern is:

SELECT *
FROM api_requests
WHERE created_at >= '2026-09-21 00:00:00'
  AND created_at < '2026-09-22 00:00:00'
ORDER BY created_at ASC;

Notice that the upper boundary uses the next day rather than:

<= '2026-09-21 23:59:59'

Using the next day as an exclusive upper boundary is generally preferable because it also works correctly when the column stores fractional seconds.

For example, a value such as:

2026-09-21 23:59:59.500000

would be excluded by <= '2026-09-21 23:59:59', even though it occurred on the requested date.

The pattern:

>= start
AND < end

is often referred to as a half-open interval and is a useful pattern for date and time filtering.

Filtering by Month

Suppose we want all API requests created during September 2026.

One option is to extract the year and month from the column:

SELECT *
FROM api_requests
WHERE YEAR(created_at) = 2026
  AND MONTH(created_at) = 9
ORDER BY created_at ASC;

This is easy to understand, but it isn't always the best approach for performance.

A range-based query is usually preferable:

SELECT *
FROM api_requests
WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-10-01 00:00:00'
ORDER BY created_at ASC;

The second query doesn't need to know how many days September has. The upper boundary simply represents the beginning of the following month.

You can also calculate that boundary with DATE_ADD():

SELECT *
FROM api_requests
WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at < DATE_ADD('2026-09-01 00:00:00', INTERVAL 1 MONTH)
ORDER BY created_at ASC;

This pattern is particularly useful when the starting date is generated dynamically.

Why Date Filtering Can Affect Performance

Date filtering isn't only about getting the correct result. The way you write the condition can also affect how MySQL accesses the data.

Consider this query:

SELECT *
FROM api_requests
WHERE CAST(created_at AS DATE) = '2026-09-21';

Or:

SELECT *
FROM api_requests
WHERE YEAR(created_at) = 2026
  AND MONTH(created_at) = 9;

Both queries apply functions to the created_at column.

If created_at has a normal index, transforming the indexed column can prevent MySQL from using that index as efficiently for the range lookup. On a small table this may not matter, but on a table containing millions of rows it can have a significant impact.

A range condition keeps the indexed column itself unchanged:

WHERE created_at >= '2026-09-21 00:00:00'
  AND created_at < '2026-09-22 00:00:00'

This allows MySQL to use range access on a suitable index.

Modern MySQL also supports indexes on expressions, so functional indexing can be an option for specific use cases. However, when a straightforward range condition expresses the same requirement, it is often the simpler approach.

The practical rule: when filtering an indexed DATETIME or TIMESTAMP column, prefer a range condition over applying functions directly to the column whenever possible.

Useful Date and Time Functions

MySQL provides many temporal functions. You don't need to memorize all of them, this quick reference covers the ones you'll reach for most often:

Function	Purpose	Example
`DATE_ADD()`	Adds an interval to a date/datetime	`DATE_ADD('2026-09-21', INTERVAL 1 MONTH)`
`DATE_SUB()`	Subtracts an interval from a date/datetime	`DATE_SUB('2026-09-21', INTERVAL 30 DAY)`
`CURDATE()`	Returns the current date	`CURDATE()`
`NOW()`	Returns the current date and time	`NOW()`
`DATE_FORMAT()`	Formats a value as a display string	`DATE_FORMAT(created_at, '%d/%m/%Y')`
`STR_TO_DATE()`	Parses a string into a date/datetime	`STR_TO_DATE('21/09/2026', '%d/%m/%Y')`
`TIMESTAMPDIFF()`	Difference between two values in a given unit	`TIMESTAMPDIFF(DAY, start, end)`
`CONVERT_TZ()`	Converts a datetime between time zones	`CONVERT_TZ(dt, 'UTC', 'America/New_York')`

Two of these are worth a closer look, since they cover two very common real-world needs.

DATE_SUB() for relative ranges. This is the go-to pattern for "the last N days" queries:

SELECT *
FROM api_requests
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at ASC;

CONVERT_TZ() for displaying local time. When an application needs to show a stored instant in a different region:

SELECT CONVERT_TZ(
    '2026-09-21 12:00:00',
    'UTC',
    'America/New_York'
);

Time-zone handling requires appropriate MySQL time-zone information to be available, so applications should also consider how time zones are configured and maintained.

Note that DATE_FORMAT() returns a string, so it's generally something to use when preparing values for display, not when filtering an indexed temporal column (see the performance section above).

A Note About TIMESTAMP Range

TIMESTAMP and DATETIME are not interchangeable in every situation.

One important difference is their supported ranges. The standard MySQL TIMESTAMP range ends in January 2038, while DATETIME supports a much wider range.

For applications that need dates outside the TIMESTAMP range, DATETIME may therefore be the appropriate choice.

This is another reason not to choose a temporal type simply because it appears to be the more modern option.

Date and Time Testing Considerations

Date-related bugs are often caused by boundary conditions rather than ordinary dates.

When testing an application that works with dates and times, consider scenarios such as:

The first and last day of a month
February 28 and February 29
Leap years
Midnight boundaries
Dates with fractional seconds
Transitions between months or years
Different application or database time zones
Daylight-saving-time transitions when local time zones are involved
Events occurring very close together
Values near the supported range of the selected data type

For example, if an API endpoint accepts a date range, don't test only:

2026-09-01 → 2026-09-21

Also test boundaries such as:

2026-09-01 00:00:00
2026-09-21 23:59:59.999999
2026-09-30 23:59:59.999999
2026-10-01 00:00:00

These cases can reveal problems involving inclusive/exclusive boundaries, precision, time-zone conversion, and date arithmetic.

Practical Patterns to Remember

For most applications, a few patterns cover a large portion of everyday date-related queries.

Filter one day

WHERE created_at >= '2026-09-21 00:00:00'
  AND created_at < '2026-09-22 00:00:00'

Filter one month

WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-10-01 00:00:00'

Filter the last 30 days

WHERE created_at >= NOW() - INTERVAL 30 DAY

Calculate the difference between dates

TIMESTAMPDIFF(DAY, start_date, end_date)

Add time to a date

DATE_ADD(start_date, INTERVAL 1 MONTH)

These patterns are generally more useful to remember than a long list of individual date functions.

Takeaways

Choose the temporal data type according to what the value represents. DATE, TIME, DATETIME, and TIMESTAMP have different purposes.
Understand the difference between DATETIME and TIMESTAMP, particularly when time zones are involved.
Prefer half-open ranges (>= start and < end) when filtering date and time intervals.
Avoid relying on 23:59:59 as the end of a day, especially when fractional seconds are possible.
When filtering indexed temporal columns, prefer range conditions over applying functions such as YEAR(), MONTH(), or CAST() directly to the column when possible.
Use DATE_ADD(), DATE_SUB(), TIMESTAMPDIFF(), DATE_FORMAT(), and CONVERT_TZ() for common date-related operations.
When testing date-related functionality, pay particular attention to boundaries, leap years, precision, and time zones.