MySQL SELECT YESTERDAY’S DATE

This article will be looking into MySQL SELECT WHERE statements with yesterday’s date in the WHERE clause. We will be going through examples to illustrate its working.

Table of Contents:

• MySQL Select Yesterday’s Date
• MySQL Select Where Date is Yesterday using CURRENT_DATE() or SUBDATE()

Let us get started by making the sample data. Create a table named user_login_details, followed by inserting some rows into it.

# create the table user_login_details;  
CREATE TABLE user_login_details (
user_id INT,
user_name VARCHAR(255),
login_date DATETIME
);
# insert rows of data to user_login_details;  
INSERT INTO user_login_details(user_id,user_name,login_date) 
VALUES(1,"Justin",'2020-12-12 19:13:30'),
(2,"Veronica",current_date()),
(3,"George",'2019-11-02 09:12:30'),
(4,"Peter",'2020-03-08 07:07:20'),
(5,"Smitha",'2019-12-06 09:12:30'),
(6,"Walter",'2019-03-06 09:12:30'),
(7,"Daniel",'2020-09-09 09:12:30'),
(8,"Atharv",'2021-04-06 06:02:25');

To view the snapshot of the table user_login_details, execute:

SELECT * FROM user_login_details;

Output:-

MySQL Select Yesterday’s Date

Example: Get Yesterday’s Date

Observe the below solutions.

SELECT CURRENT_DATE()-1 AS YESTERDAY;

Output:-

SELECT SUBDATE(CURRENT_DATE, 1) AS YESTERDAY;

Output:-

As we can see, using SUBDATE(date, interval) is a better option as it produces a more readable output.

Here in the above queries we are using MySQL functions CURRENT_DATE() and SUBDATE()

• SUBDATE(date, interval_expression_unit): This function performs an arithmetic operation on the date passed as the first argument. The subdate() function will subtract the interval passed as the second argument from the date given as the first argument.
• CURRENT_DATE(): Will return current date either in ‘YYYY-MM-DD‘ or ‘YYYYMMDD‘ format depending on if the current_date() function is used in a string or numeric context.

MySQL Select Where Date is Yesterday using CURDATE() or SUBDATE() function

Example: Get the details of users who have logged in yesterday.

Observe the below queries for the solution.

SELECT * FROM user_login_details WHERE DATE(login_date) = CURRENT_DATE()-1;

OR

SELECT * FROM user_login_details WHERE DATE(login_date) = SUBDATE(CURRENT_DATE, 1);

Both the queries will return the same output as shown below.

Action Output Message:-

2 row(s) returned

Output:-

The output in image_4 is showing two records where the user logged in yesterday.

• NOW() function will return the current date and time.
• DATE(expression): Will get the date value from the date or datetime expression passed in as an argument.

READ MORE

• MySQL SELECT WHERE DATE IS TODAY
• MySQL SELECT WHERE
• MYSQL SELECT WHERE MONTH AND YEAR
• MySQL SELECT WHERE NOT IN
• MySQL SELECT WHERE LIKE
• MySQL SELECT WHERE NULL

We hope this article helped you with MySQL select statements where the date is yesterday's date. Good Luck!!!.