MySQL Date Compare: Techniques and Best Practices

Table of contents

- Examples of Syntax DATE and TIMESTAMP
- Demo Setup
- An overview of the DATE() function
- Compare two dates using the WHERE clause
- Compare two dates using logical and arithmetic operators
- Compare two dates using DATEDIFF() function
- Compare dates using dbForge MySQL Studio
- Compare the dates on all records of the table
- Summary

This article will give us a walk-through of different methods to compare datetime values in MySQL. In MySQL, we can store dates in DATE and TIMESTAMP data types. Both data types store data in YYYY-MM-DD HH:MM: SS format. There are a few differences between both datatypes.

1. The DATETIME supports ‘1000–01–01 00:00:00’ to ‘9999–12–31 23:59:59’ but TIMESTAMP supports ‘1970–01–01 00:00:01’ to ‘2038–01–19 03:14:07 UTC.
2. The DATETIME is constant, but the TIMESTAMP values change based on the timezone the application uses because the TIMESTAMP data will convert the current time to UTC and vice-versa.

Examples of Syntax DATE and TIMESTAMP

Following is a simple example that explains the difference between both data types.

Query 1: DATE TIME datatype

Query 2: TIMESTAMP datatype

Now, change the timezone from IST (Indian Standard Time) to CST (Central Standard Time). After changing timezones, the query output changes like the following:

Query 1: DATE TIME datatype

Query 2: TIMESTAMP datatype

The above screenshot shows that the timestamp is converted to the CST (UTC -6:00) timezone.

This article contains various queries, so I have prepared a demo setup on my computer to understand it more clearly. The details are following:

Demo Setup

For demonstration, I have created a table named tblCustomer in the sakila database. The SQL Code to create the table is below.

USE sakila;
CREATE TABLE tblCustomer (
customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
email VARCHAR(50) DEFAULT NULL,
rental_id smallint UNSIGNED NOT NULL,
rental_date datetime,
return_date datetime,
PRIMARY KEY (customer_id)
);

I have inserted dummy records in a tblCustomer table. The SQL Code to insert data is the following:

INSERT INTO sakila.tblcustomer(customer_id, first_name, last_name, email, rental_id, rental_date, return_date) VALUES
(1, 'PATRICIA', 'JOHNSON', 'PATRICIA.JOHNSON@sakilacustomer.org', 320, '2005–05–27 00:09:24', '2005–05–28 04:30:24'),
(2, 'TYLER', 'WREN', 'TYLER.WREN@sakilacustomer.org', 322, '2005–05–27 00:47:35', '2005–06–02 00:32:35'),
(3, 'CLARA', 'SHAW', 'CLARA.SHAW@sakilacustomer.org', 323, '2005–05–27 00:49:27', '2005–05–30 03:12:27'),
(4, 'DAVE', 'GARDINER', 'DAVE.GARDINER@sakilacustomer.org', 487, '2005–05–28 00:00:30', '2005–05–28 19:18:30'),
(5, 'REGINA', 'BERRY', 'REGINA.BERRY@sakilacustomer.org', 488, '2005–05–28 00:07:50', '2005–06–03 22:30:50'),
(6, 'DERRICK', 'BOURQUE', 'DERRICK.BOURQUE@sakilacustomer.org', 489, '2005–05–28 00:09:12', '2005–05–31 04:44:12'),
(7, 'SUE', 'PETERS', 'SUE.PETERS@sakilacustomer.org', 683, '2005–05–29 00:09:48', '2005–06–02 04:27:48'),
(8, 'DEREK', 'BLAKELY', 'DEREK.BLAKELY@sakilacustomer.org', 684, '2005–05–29 00:13:15', '2005–06–04 21:26:15'),
(9, 'BERNICE', 'WILLIS', 'BERNICE.WILLIS@sakilacustomer.org', 685, '2005–05–29 00:17:51', '2005–06–05 05:32:51'),
(10, 'NORMAN', 'CURRIER', 'NORMAN.CURRIER@sakilacustomer.org', 686, '2005–05–29 00:27:10', '2005–05–30 02:29:10'),
(11, 'JO', 'FOWLER', 'JO.FOWLER@sakilacustomer.org', 687, '2005–05–29 00:32:09', '2005–05–31 23:53:09'),
(14, 'BERNICE', 'WILLIS', 'BERNICE.WILLIS@sakilacustomer.org', 837, '2005–05–30 00:02:08', '2005–06–02 05:31:08'),
(15, 'NATHANIEL', 'ADAM', 'NATHANIEL.ADAM@sakilacustomer.org', 838, '2005–05–30 00:27:57', '2005–06–06 22:19:57'),
(17, 'DANIELLE', 'DANIELS', 'DANIELLE.DANIELS@sakilacustomer.org', 995, '2005–05–31 00:06:02', '2005–06–06 02:30:02'),
(18, 'ERIC', 'ROBERT', '@sakilacustomer.org">ERIC.ROBERT@sakilacustomer.org', 996, '2005–05–31 00:06:20', '2005–05–31 21:29:20'),
(19, 'NATALIE', 'MEYER', 'NATALIE.MEYER@sakilacustomer.org', 997, '2005–05–31 00:08:25', '2005–06–02 00:17:25'),
(20, 'ALAN', 'KAHN', 'ALAN.KAHN@sakilacustomer.org', 998, '2005–05–31 00:16:57', '2005–06–01 22:41:57'),
(21, 'MARVIN', 'YEE', 'MARVIN.YEE@sakilacustomer.org', 999, '2005–05–31 00:25:10', '2005–06–03 06:05:10');

An overview of the DATE() function

The DATE() function shows the only date part from the datetime expression. If the expression is NULL, then it also returns NULL. Following is the syntax of the DATE() function.

Select DATE(datetime expression)

Let us take a simple example. We want to display the date part of rental_date from the tblCustomer table. To achieve that, we can write the query as follows:

Output

As you can see, the query extracted the date part of the rental_date column.

The DATE() function compares a string expression with a datetime value. For example, we want to extract the list of customers whose rental date is 28–05–2005. To do that, the query is written as follows:

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date "Rental Date" from tblcustomer where rental_date='2005–05–28';

Output:

As you can see, the query did not return any records because when we do not include the DATE() function, the query includes the time portion of the datetime column. So if you re-write the query and include the time portion, it will extract the desired records.

Query

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date "Rental Date" from tblcustomer where rental_date='2005–05–28 00:00:30';

Output

Now, lets us include the DATE function in a query.

Query

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date "Rental Date" from tblcustomer where date(rental_date)='2005–05–28';

Output

As you see, the query returned a list of customers whose rental date is 2005–05–28. In this case the index is not used.

The DATE() function can be used with various clauses, operators, and functions. Let us understand them with simple examples.

Compare two dates using the WHERE clause

Suppose we want to populate the list of employees whose rental date is greater than “2005–05–27” and whose return date is less than “2005–06–01”. The query is written as follows:

Query

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", , rental_date , return_date from tblcustomer where date(rental_date)>'2005–05–27' and date(return_date)<'2005–06–01'

Output

As you can see in the above screenshot, we have added the DATE() function in the WHERE clause.

Now, let us see how we can use logical and arithmetic operators to compare two dates.

Compare two dates using logical and arithmetic operators

For example, we want to show the list of the customers whose rental date is greater than “2005–05–30” To do that, we will use the> (Arithmetic Operator) Operator. The query is below:

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date , return_date from tblcustomer where date(rental_date)>'2005–05–30'

Output

Let us take another example; we want to populate the list of customers whose return date is between “2005–05–31” and “2005–06–03”. To do that, we are using BETWEEN (Logical Operator). The query is below:

use sakila;
select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date , return_date from tblcustomer where date(return_date) between '2005–05–31' and '2005–06–03'

Output

Now, let us see how we can use the function to compare dates. The index is used in this сase.

Compare two dates using DATEDIFF() function

Suppose we want to count of days between rental_date and return_date. To do that, we are using DATEDIFF() function. The query is following:

use sakila;
Select customer_id "CustomerID",first_name "First Name",last_name "Last Name",email "Email ID", rental_date,return_date, datediff(date(return_date),date(rental_date))"TotalDays" from tblcustomer Where date(rental_date)>='2005–05–30'

Output

Compare dates using dbForge MySQL Studio

The dbForge MySQL Studio has a great feature that helps to filter data from tables by comparing the dates. We can use this option in two ways

1. Compare the dates on all records of a table.
2. Compare the dates on the results set.

Compare the dates on all records of the table

The dbForge MySQL Studio allows viewing all records in a data viewer tab. To do that, launch dbForge Studio for MySQL 🡪 Connect to sakila database 🡪 Expand sakila 🡪 Expand tables 🡪 Right-click tblCustomer 🡪 Select Retrieve Data. Following is the screenshot of database explorer.

As shown below, the data of the tblCustomer table will be displayed in a data viewer tab.

Now, we want to compare the rental date of all customers and populate the list of the customers whose rental date exceeds 2005–05–30. To do that, right-click on the rental_date column and select the filter option.

A dialog box filter option opens. Here you can add multiple conditions that can be used to filter the data of a table.

We want to get the list of customers whose rental date is greater than 30–05–2005. To do that, the filter must be set as shown below

Click Apply.

As you can see in the above image, the data of tblCustomer is filtered, and you can see the list of customers whose rental date is greater than 2005–05–31.

Here I would like to show another interesting feature of dbForge Studio for MySQL 2022. It allows us to update the records directly from query output. For example, I want to update the rental_date of the customer name ALAN KHAN. The current value is 31–05–2005 00:16:57, and I want to change it to 01–06–2005 00:16:57. To do that, click on the rental_date of ALAN KHAN. A calendar will open as shown in the following image:

Change the rental_date to 01–06–2005 00:16:57 and click on the refresh data button on the top left side of the output toolbar. To verify the changes, run a SELECT query on the tblCustomer table.

As you can see in the above image, the rental_date of the ALAN KHAN user has been changed. This feature must be used with caution to avoid unexpected data changes in the base table of the application.

Summary

This article explains how we can store date values in MySQL. Also, we learned about the DATETIME and TIMESTAMP data types and the difference between them. Also, I explained various methods to compare dates in MySQL. The dbForge Studio for MySQL makes the process of comparing dates much easier. You can filter records directly from the output without writing any SQL queries.