MySQL Date Compare – DZone

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 the “YYYY-MM-DD” “HH:MM:SS” format. There are a few differences between both data types, such as:

• The DATETIME supports “1000-01-01 00:00:00” to “9999-12-31 23:59:59,” but the TIMESTAMP supports “1970-01-01 00:00:01” to “2038-01-19 03:14:07” UTC.
• 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.

Differences Between Both Data Types

The following is a simple example that explains the difference between both data types:

Query 1: DATETIME 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: DATETIME 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', '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');

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. The 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 the 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:

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, let us include the DATE function in a 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:

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 the DATEDIFF() Function

Suppose we want to count the days between the 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 MySQLConnect to sakila database.
• Expand sakila
• Expand tables
• Right-click tblCustomer
• Select “Retrieve Data.” 

The following is the screenshot of the 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:

Next, 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 the query output. For example, I want to update the rental_date of the customer who is named 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.