How to Compare Dates in MySQL

Overview of MySQL date data types

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

Further, we'll explore the approaches to compare dates in MySQL. As a prerequisite, we have created a Persons table in the sakila database and populated it with data using the Studio's integrated Data Generator.

DATE() syntax in MySQL

As we mentioned earlier, a date in a MySQL table column can have either DATE, DATETIME, TIMESTAMP, TIME, or YEAR value. If the input date value has a mismatch with the data type of the date stored in the column, you can convert a string into a value of the DATE data type. This can be done with the help of the DATE() function, which extracts the date part from the specified date/datetime expression. The syntax for the DATE() function is as follows:

DATE ('YYYY-MM-DD');

For example, if you try to retrieve some hire dates from the Persons table by executing a SELECT statement with the specified condition in the WHERE clause, the DATE() function will return the date part without the time part as specified in the condition. To execute queries against your MySQL tables, you can use dbForge Studio, as in the following example.

Compare dates in MySQL

Using the comparison operators is the simplest way to compare dates in MySQL. They can help you define whether a given date value equals or is greater/less than the date values stored in the database table. Let's see an example of comparing dates in MySQL using these operators.

Imagine that you have been asked to prepare a list of employees who were hired from January 1, 2020 to December 31, 2021. To do this, you need to execute a SELECT statement with the comparison operators such as greater than or equal (>=) or less than or equal (<=), both of which must be specified 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 date with the dates stored in the HireDate column and returns the dates matching the range specified in the WHERE clause. This is what it looks like in dbForge Studio.

Compare dates within a specified range in MySQL

You can compare dates within a range. For this, you can use 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 values specifying the range within which you want to select dates.

The output returns the values that match the specified date range in the WHERE clause.

Use the DATE_ADD function

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 unit is a type of interval, such as year, month, day, hours, minutes, seconds, etc.

For example, here's a query:

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

This query returns a new date - April 19, 2023 - which is 3 months greater than the specified one.

Compare a specified date with the current date in MySQL

If you need to compare any given date with the current date, you can use the NOW() function in the WHERE clause. The function fetches the current date.

Take a look at the following query:

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

Now let's proceed to comparing a timestamp date with the date part of the date value. In this case, you will need to convert a datetime value to a date value.

To do this, you can apply the CAST() function, which converts values from one data type to another. It accepts the following parameters:

CAST(value AS datatype);

In this syntax, value is the value to be converted, and datatype is the data type that it must be converted to.

Here is an example of retrieving dates stored in the create_date column of the customer table. The specified condition says that the date part of the datetime values should be equal to the value specified in the WHERE clause.

As you can see, MySQL compares the input date value to the dates stored in the customer table and returns the dates according to the given condition.

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 DATETIME value to be converted
• from_tz is the current time zone of your DATETIME value
• to is the time zone to which the DATETIME value must be converted

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 helpful 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 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

Whichever method you prefer, it will all boil down to writing and running a query. And, for sure, you don't want it to be resource-consuming. To keep track of query performance, you can use the Studio's integrated tool called Query Profiler. It will help you save time on inspecting slow queries and detecting performance bottlenecks. The capabilities of Query Profiler include but are not limited to the following:

• Inspect EXPLAIN plans
• Examine session statistics
• View the text of executed queries
• Compare query profiling results
• Identify and troubleshoot slow queries

Conclusion

This is how you can compare dates using functions and comparison operators. All the examples were demonstrated in dbForge Studio for MySQL. Due to the variety of features and capabilities that the Studio has, such as the MySQL syntax checker or MySQL code formatter, a single IDE will be enough to perform different database development, management, testing, analysis, and deployment tasks. Get the Studio for a free 30-day trial and give it a go today!