How to get the records of the last two days from the current date in MySQL?

To get the records of the last days from the current date, you need to use DATE_SUB(). We will also use NOW() to get the current date. The syntax for the same is as follows

SELECT *FROM yourTableName
WHERE yourDateTimeColumnName BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 2 DAY)
AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);

Let us see an example

mysql> create table get2daysAgoDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > Name varchar(20),
   - > PostDateTime datetime
   - > );
Query OK, 0 rows affected (1.70 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('Larry',now());
Query OK, 1 row affected (0.68 sec)
mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('Mike','2019-02-13');
Query OK, 1 row affected (0.12 sec)
mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('Sam','2019-01-31');
Query OK, 1 row affected (0.15 sec)
mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('Bob','2019-02-14');
Query OK, 1 row affected (0.29 sec)
mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('David','2019-02-12');
Query OK, 1 row affected (0.21 sec)
mysql> insert into get2daysAgoDemo(Name,PostDateTime) values('Carol','2019-02-11');
Query OK, 1 row affected (0.22 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from get2daysAgoDemo;

The following is the output

+----+-------+---------------------+
| Id | Name  | PostDateTime        |
+----+-------+---------------------+
|  1 | Larry | 2019-02-15 21:47:10 |
|  2 | Mike  | 2019-02-13 00:00:00 |
|  3 | Sam   | 2019-01-31 00:00:00 |
|  4 | Bob   | 2019-02-14 00:00:00 |
|  5 | David | 2019-02-12 00:00:00 |
|  6 | Carol | 2019-02-11 00:00:00 |
+----+-------+---------------------+
6 rows in set (0.00 sec)

Here is the query to get the records of the last two days. Let’s say the current date is 2019-02-15

mysql> select *from get2daysAgoDemo
   - > where PostDateTime between DATE_SUB(DATE(NOW()), INTERVAL 2 DAY)
   - > AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);

The following is the output displaying the records from the last 2 days i.e. 13th and 14th February sine our current date is 15th February

+----+------+---------------------+
| Id | Name | PostDateTime        |
+----+------+---------------------+
|  2 | Mike | 2019-02-13 00:00:00 |
|  4 | Bob  | 2019-02-14 00:00:00 |
+----+------+---------------------+
2 rows in set (0.01 sec)

Arjun Thakur

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Fetch records from interval of past 3 days from current date in MySQL and add the corresponding records
• MySQL query to get the current date records wherein one of the columns displays current date
• Comparison of varchar date records from the current date in MySQL
• How to get the difference between date records and the current date in MySQL?
• Get the number of days between current date and date field?
• Get the last days of all the months in MySQL?
• How to compare the first date and the last date from a MySQL column with date records?
• MySQL query to get the current date from the list of dates
• MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
• Java Program to get date for all the days of the current week
• Display records from the current date till rest of the same month in MySQL?
• Find the difference between current date and the date records from a MySQL table
• Fetch student records whose result declared 12 days before the current date in MYSQL
• How to get last day of the current month in MySQL?
• How to get first and last date of the current month with JavaScript?