How to Compare Dates in MySQL

Overview of MySQL date data types

What types of date and time data can be stored in MySQL? Understanding MySQL date types is essential for accurately comparing and filtering time-based information. Each type is designed for a specific use case, whether you only need a year, a date, or a complete timestamp with microsecond precision.

Date data types in MySQL store date and/or time values in a database table. They are as follows:

Now, let's explore the approaches for comparing dates in MySQL. As a prerequisite, we have created a Persons table in the sakila database and populated it with data using the dbForge Studio's integrated Data Generator.

DATE() syntax in MySQL

Use the DATE() function when you need to extract or compare only the date portion of a DATETIME value, ignoring the time part. This is useful when the column contains DATETIME or TIMESTAMP values, but your query only cares about the day, month, and year. By using DATE(), you can ensure accurate comparisons and filtering without them being affected by hours, minutes, or seconds.

When should I use the DATE() function in MySQL?

The DATE() function is helpful whenever the input value might not match the data type of the column, or when you want to convert a string or a full DATETIME into a date for comparison. The syntax is simple:

DATE('YYYY-MM-DD');

For example, if you want to retrieve hire dates from the Persons table while ignoring the time portion:

SELECT PersonID, LastName, FirstName, DATE(HireDate) AS hire_day
FROM Persons
WHERE DATE(HireDate) = '2021-05-01';

Even if HireDate includes timestamps like 2021-05-01 09:45:00, the query will match because the DATE() function strips off the time.

You can also use DATE() to convert a DATETIME string during comparisons. For instance, to find orders placed on a specific day:

SELECT *
FROM Orders
WHERE DATE(order_datetime) = DATE('2023-07-15 14:30:00');

In this query, MySQL converts the DATETIME string to a pure date, allowing the comparison to work correctly regardless of the time component.

How to compare two dates using the WHERE clause in MySQL?

To filter records between specific dates, use comparison operators like >= and <=. These operators allow you to define whether a date in the table is greater than, less than, or equal to a given value.

For example, imagine you need to prepare a list of employees who were hired from January 1, 2020, to December 31, 2021. You can write a SELECT statement with comparison operators in the WHERE clause:

SELECT
    p.PersonID,
    p.LastName,
    p.FirstName,
    p.city,
    p.HireDate
FROM persons p
WHERE p.HireDate >= '2020-01-01'
AND p.HireDate <= '2021-12-31'
ORDER BY p.HireDate ASC;

MySQL compares the specified dates with the values stored in the HireDate column and returns only the rows that match the specified range. This query is easy to read in tools like dbForge Studio for MySQL.

Compare dates within a specified range in MySQL

How to select rows within a date range in MySQL? You can compare dates within a range by using the BETWEEN operator in your SELECT statement:

SELECT columns 
FROM table_name 
WHERE column BETWEEN value1 AND value2;

In this syntax, value1 and value2 are the boundaries that define the range of dates you want to filter. The output returns all rows where the column value falls between these dates. The same result can also be achieved by using comparison operators (>= and <=):

SELECT columns 
FROM table_name 
WHERE column >= value1 
  AND column <= value2;

Both approaches work, but BETWEEN is often preferred because it is more concise and easier to read. Remember that BETWEEN is inclusive, and it will return rows that match both the start and end dates.

How do I use DATE_ADD to calculate future dates in MySQL?

To compare dates, you can also use the DATE_ADD function, which returns the date to which the specified time or date interval was added.

The syntax for the DATE_ADD function is as follows:

DATE_ADD(date, INTERVAL value expression unit);

In this syntax:

• date is the date to be modified.
• value is the date or time interval to be added.
• expression is an expression specifying the interval value to be added or subtracted from the starting date. It is evaluated as a string, and it may start with a “-” for negative intervals.
• unit is a keyword indicating the units in which the expression should be interpreted.

For example, if you want to add three months to a specific date, you can run:

SELECT DATE_ADD('2023-01-19', INTERVAL 3 MONTH);

This query returns 2023-04-19, which is exactly three months after the given date.

Therefore, to calculate a future or past date in MySQL, feel free to use the DATE_ADD function. It is highly flexible and works with various intervals, including DAY, MONTH, YEAR, HOUR, and SECOND. It's handy when you need to calculate deadlines, renewal dates, or any time-based conditions directly in your SQL queries.

For instance, imagine you run a subscription service where each user's plan lasts 30 days from the date of signup. You can easily calculate the expiration date with the following query:

SELECT user_id, signup_date, DATE_ADD(signup_date, INTERVAL 30 DAY) AS expiration_date
FROM users;

This will return a list of users along with the date their subscription is set to expire, helping you manage notifications, renewals, or automated cleanup tasks.

Compare a specified date with the current date in MySQL

Sometimes, you need to filter records against the exact moment when a query is executed. For example, it might be used for retrieving rows created today or checking whether a subscription has already expired. In such cases, MySQL provides the NOW() function, which returns the current date and time according to the server's system clock. This makes it especially valuable for real-time filtering and evaluating current timestamps in your queries.

How to compare a column with the current date in MySQL?

To compare dates with the current date in MySQL, use the NOW() function. For instance, you might want to return all rows created today:

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  date(c.create_date)
FROM customer c
WHERE  DATE(c.create_date) = date(NOW());

The date values from the create_date column stored in the customer table will be compared with the current date. Then, the SELECT statement will extract only those dates that match the current date. Note that the current date used in this example is January 20, 2023.

MySQL timestamp comparison

To compare a TIMESTAMP or DATETIME with a DATE value, use CAST() or DATE() to normalize formats. This ensures that the comparison only considers the relevant parts of the values and avoids mismatches due to differing data types.

How to compare timestamp values with date values in MySQL?

Let's say your datetime column includes timestamps, but you only care about the date part for filtering or reporting. You can use the CAST() function to convert the DATETIME or TIMESTAMP value to a DATE:

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  date(c.create_date)
FROM customer c
WHERE CAST(create_date AS DATE) = '2023-01-20';

Alternatively, you can use the DATE() function to achieve the same result:

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  date(c.create_date)
FROM customer c
WHERE DATE(create_date) = '2023-01-20';

Both approaches normalize the timestamp to a date, allowing MySQL to compare it with the specified DATE value. This ensures that only the rows matching the date portion are returned, regardless of the time component.

Common issues you may encounter when comparing dates

Let's pinpoint a few potential issues that may be encountered when performing date comparison operations.

1. Make sure you avoid any inconsistent date formats when comparing dates. You can use the DATE_FORMAT function to format your dates as specified.

DATE_FORMAT(date, format)

In this syntax:

• date is the date to be formatted.
• format is the format you want to apply; you will find a list of possible formats here.

2. If you are comparing dates spanning across different time zones, you might want to have them normalized to the same time zone. To do that in MySQL, you can use the CONVERT_TZ function, which returns a DATETIME value converted to the specified time zone. The syntax for this function is as follows:

CONVERT_TZ(dt, from_tz, to_tz)

In this syntax:

• dt is the original DATETIME or TIMESTAMP value to be converted.
• from_tz is the time zone of the original value. It can be specified as 'UTC', 'SYSTEM', a named time zone (such as 'Europe/Prague'), or an offset (like '+02:00').
• to_tz is the target time zone to which the value should be converted. It follows the same format options as from_tz.

3. You should also be careful with zero dates (e.g., '0000-00-00'). If you use them as an input for functions like YEAR(), they may result in NULL.

A few helpful tips on date comparison

Now that we're through with possible issues, let's focus on several useful tips that will help your queries return accurate results.

• When working with large datasets, make sure your date columns are properly indexed; this will speed up the execution of your queries.
• Partitioning of data by date is one of the common uses of partitioning in general. While some database systems support explicit date partitioning, the latest versions of MySQL do not implement it. However, you still can conduct partitioning based on DATE, TIME, or DATETIME columns, or based on expressions that make use of such columns.
• If your query includes a BETWEEN operator, you need to keep your date format consistent to prevent inaccurate results.
• If you are comparing dates in dynamic queries, you can apply prepared statements to protect your databases against possible SQL injections and ensure the correct processing of your date format.
• In case you need to regularly remove outdated rows (for example, those whose DATETIME exceeds 1 year), you can do it using a query like DELETE FROM table WHERE date < NOW() - INTERVAL 1 YEAR. However, you need to be careful with this operation as you may accidentally lose data.

Query optimization

To improve the performance of date-based queries in MySQL, consider the following practices to make your queries faster and more efficient.

Here are some key optimization tips for date-based queries:

• Use indexes on date columns: Properly indexed DATE, DATETIME, or TIMESTAMP columns allow MySQL to quickly filter rows without scanning the entire table.
• Avoid functions in the WHERE clause when possible: Using functions like DATE() or CAST() directly on a column can prevent MySQL from using indexes efficiently. Consider storing precomputed date values or restructuring queries.
• Limit the range of scanned data: Whenever possible, filter using explicit ranges (>= and <=) or partition data to reduce the number of rows scanned.

How to optimize MySQL queries with date filters?

Whichever method you prefer, it will all boil down to writing and running a query with a date filter. And, for sure, you don't want it to be resource-consuming. To keep track of query performance, you can use dbForge Studio for MySQL and its integrated tool called Query Profiler. It helps inspect slow queries and detect performance bottlenecks.

The capabilities of Query Profiler include:

• Inspecting EXPLAIN plans
• Examining session statistics
• Viewing the text of executed queries
• Comparing query profiling results
• Identifying and troubleshooting slow queries

By following these practices, you can ensure your date-based queries run efficiently, saving both time and server resources.

Conclusion

So, what did we learn about MySQL date comparison? Working with dates in MySQL can seem tricky at first, but there are several effective techniques to handle different scenarios. Here's a quick recap of what we covered:

• Comparison operators (>=, <=, =): Use these to filter rows between specific dates or check for exact matches.
• BETWEEN operator: Ideal for selecting rows within a date range in a concise and readable way.
• DATE() function: Extracts the date part from DATETIME or TIMESTAMP values for accurate comparisons.
• NOW() function: Fetches the current timestamp for real-time filtering based on the system clock.
• DATE_ADD() function: Adds intervals to dates, useful for calculating future or past dates.
• CAST() function: Converts DATETIME or TIMESTAMP values to DATE for normalized comparisons.

All examples in this tutorial were demonstrated using dbForge Studio for MySQL, which offers syntax checking, code formatting, and query profiling to simplify date-based operations. These techniques allow you to handle date filtering, reporting, and data analysis efficiently and accurately.