This article will give us a walk-through of different methods to compare datetime values in MySQL. In MySQL, we can store dates in
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:
DATETIMEsupports “1000-01-01 00:00:00” to “9999-12-31 23:59:59,” but the
TIMESTAMPsupports “1970-01-01 00:00:01” to “2038-01-19 03:14:07” UTC.
DATETIMEis constant, but the
TIMESTAMPvalues change based on the timezone the application uses because the
TIMESTAMPdata 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:
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
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
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:
As you can see, the query extracted the date part of 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";
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";
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';
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.
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'
As you can see in the above screenshot, we have added the
DATE() function in the
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'
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'
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
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'
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:
- Compare the dates on all records of a table.
- 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
- 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
ALAN KHAN. A calendar will open as shown in the following image:
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
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.
This article explains how we can store date values in MySQL. Also, we learned about the
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.
Leave a Reply