CODEWITHSUNDEEP
mysqlsqlmariadbintervals
Karlom
Karlom

Reputation: 14834

How can I select rows newer than a week?

Using MariaDB 10, I'd like to query article table for the past week articles:

Here is my query:

SELECT * FROM article WHERE category="News" AND created_at < NOW() - INTERVAL 1 WEEK ORDER BY created_at DESC;

But it returns all articles instead.

explain article ;

+-------------+-----------------+------+-----+-------------------+----------------+
| Field       | Type            | Null | Key | Default           | Extra          |
+-------------+-----------------+------+-----+-------------------+----------------+
| id          | int(6) unsigned | NO   | PRI | NULL              | auto_increment |
| title       | varchar(150)    | NO   |     | NULL              |                |
| content     | mediumtext      | NO   |     | NULL              |                |
| created_at  | timestamp       | NO   |     | CURRENT_TIMESTAMP |                |
| category    | varchar(64)     | NO   |     | test              |                |

How can I achieve this?

Upvotes: 2

Views: 335

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The logic is backwards. You want > not <:

SELECT a.*
FROM article a
WHERE category = 'News' AND
      created_at > NOW() - INTERVAL 1 WEEK
ORDER BY created_at DESC;

For performance, you would want an index on article(category, created_at).

Upvotes: 3

Related Questions