How To Select Rows From Last 'N' Days Using MySQL Or MariaDB?

In this article we'll explore ways to select items added/modified in the last n number of days using MySQL (or MariaDB). For the purpose of this article, let's assume the following:

1. We wish to find all the users who've logged in in the last 7 days;
2. Today's date is 2018-06-12 (i.e. June 12, 2018);
3. The structure of our table is the following:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  1 |  jane  | 2018-06-12 |
|  2 |  john  | NULL       |
|  3 |  david | 2018-05-24 |
|  4 |  zayne | 2018-06-05 |
+----+--------+------------+

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5) NOT NULL,
    date_login DATE,
    PRIMARY KEY (id)
);

INSERT INTO user(name, date_login) VALUES
('jane', '2018-06-12'), 
('john', NULL), 
('david', '2018-05-24'), 
('zayne', '2018-06-05');

Directly Comparing With Today's Date

You could simply see if login date is greater than the date 7 days ago, like so:

Using NOW():

SELECT *, (DATE(NOW()) - INTERVAL 7 DAY) AS diff
FROM user 
WHERE date_login >= (DATE(NOW()) - INTERVAL 7 DAY)
ORDER BY date_login DESC;

If you use NOW() without DATE(), it will include current date and time.

Using CURDATE():

SELECT *, (CURDATE() - INTERVAL 7 DAY) AS diff
FROM user 
WHERE date_login  >= CURDATE() - INTERVAL 7 DAY
ORDER BY date_login DESC;

Using CURRENT_DATE:

SELECT *, (CURRENT_DATE - INTERVAL 7 DAY) AS diff
FROM user 
WHERE date_login >= CURRENT_DATE - INTERVAL 7 DAY
ORDER BY date_login DESC;

Result:

+----+--------+------------+------------+
| id |  name  | date_login |    diff    |
+----+--------+------------+------------+
|  1 |  jane  | 2018-06-12 | 2018-06-05 |
|  4 |  zayne | 2018-06-05 | 2018-06-05 |
+----+--------+------------+------------+

Using BETWEEN Operator

SELECT *
FROM user
WHERE date_login BETWEEN DATE(NOW()) - INTERVAL 7 DAY AND DATE(NOW())
ORDER BY date_login DESC;

Result:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  1 |  jane  | 2018-06-12 |
|  4 |  zayne | 2018-06-05 |
+----+--------+------------+

Using DATEDIFF Function

You could use the DATEDIFF() function to calculate the difference in days between two date values, like so:

SELECT *, (DATEDIFF(NOW(), date_login)) AS diff 
FROM user 
WHERE DATEDIFF(NOW(), date_login) <= 7
ORDER BY date_login DESC;

Result:

+----+--------+------------+--------+
| id |  name  | date_login |  diff  |
+----+--------+------------+--------+
|  1 |  jane  | 2018-06-12 |    0   |
|  4 |  zayne | 2018-06-05 |    7   |
+----+--------+------------+--------+

DATEDIFF() only takes into account the dates and not the time.

Using DATE_SUB Function

DATE_SUB function has the following syntax:

DATE_SUB(date, INTERVAL expr unit)

It subtracts the INTERVAL value with the date, for example:

SELECT *, (DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)) AS diff
FROM user 
WHERE date_login >= DATE_SUB(DATE(NOW()), INTERVAL 7 DAY) 
ORDER BY date_login DESC;

Or, alternatively, we could use DATE_ADD() function which has the same syntax as DATE_SUB. This would work by simply using a negative interval for the expression, for example:

SELECT *, (DATE_ADD(DATE(NOW()), INTERVAL -7 DAY)) AS diff
FROM user 
WHERE date_login >= DATE_ADD(DATE(NOW()), INTERVAL -7 DAY) 
ORDER BY date_login DESC;

Result:

If you use NOW() without DATE(), it will include current date and time.

Result:

+----+--------+------------+------------+
| id |  name  | date_login |    diff    |
+----+--------+------------+------------+
|  1 |  jane  | 2018-06-12 | 2018-06-05 |
|  4 |  zayne | 2018-06-05 | 2018-06-05 |
+----+--------+------------+------------+

Using SUBDATE Function

The SUBDATE() function can be used in the following two ways:

1. SUBDATE(expr, days): This syntax allows the use of an integer value for days (i.e. it is interpreted as the number of days to be subtracted from the date or datetime expression expr.
2. SUBDATE(date, INTERVAL expr unit): When using INTERVAL as the second argument, SUBDATE() is a synonym for DATE_SUB().

Consider the two examples below:

SELECT *, (SUBDATE(DATE(NOW()), 7)) AS diff
FROM user 
WHERE date_login >= SUBDATE(DATE(NOW()), 7) 
ORDER BY date_login DESC;

Or, using INTERVAL:

SELECT *, (SUBDATE(DATE(NOW()), INTERVAL 7 DAY)) AS diff
FROM user 
WHERE date_login >= SUBDATE(DATE(NOW()), INTERVAL 7 DAY)
ORDER BY date_login DESC;

Or, alternatively, we could use ADDDATE() function which has the same syntax as SUBDATE. This would work by simply using a negative interval or a negative integer value for days, for example:

SELECT *, (ADDDATE(DATE(NOW()), -7)) AS diff
FROM user 
WHERE date_login >= ADDDATE(DATE(NOW()), -7)
ORDER BY date_login DESC;

If you use NOW() without DATE(), it will include current date and time.

Result:

+----+--------+------------+------------+
| id |  name  | date_login |    diff    |
+----+--------+------------+------------+
|  1 |  jane  | 2018-06-12 | 2018-06-05 |
|  4 |  zayne | 2018-06-05 | 2018-06-05 |
+----+--------+------------+------------+

Selecting The Top Viewed In The Last 'N' Days

Since this is a popular usecase, we thought of touching on the subject.

Using any one of the techniques explained in this article you could retrieve the top viewed items from last n number of days. All you would need to do for that is add an additional database column for hits or views (or something similar), that determines the 'views' or 'hits', etc. for that particular item. After that, it's simply a matter of using the ORDER BY clause to sort the result set.