What Is The Optimal Way To Select Rows From Last 7 Days?

Question

What's the best way to select only those rows from the table that have been created in last 7 days?

There are dozens of time and date functions in MySQL and I'm a little bit confused about what's the easiest way to do this.

For the sake of this question, assume that you have a table called "my_table" and it contains a row "created_at" which is a DATETIME.

SELECT * FROM my_table WHERE ...

What would you fill in the WHERE clause?

Answer 1

...WHERE created_at >= Date_Add(now(), INTERVAL -7 DAY)

This is my preferred way because it's so...clear. But ADDDATE is fine too (and you can use the INTERVAL form with that for clarity as well; its default is days so you see people leaving it off). You don't want to do a calculation on created_at and compare it to now() because that requires the computation on created_at on each row (assuming MySQL doesn't optimise it out), whereas modifying now() and comparing to an unmodified created_at means MySQL does that bit once and uses the result when comparing against rows, not to mention indexes.

Answer 2

 WHERE  ADDDATE(datefield, 7) > NOW();

Answer 3

...... WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

hopefully that will help

Answer 4

WHERE DATEDIFF(NOW(), created_at) <= 7;

I like it because it reads: "Where the Difference in Date between Now and when it was created is at most 7 (days)." in my own head

Answer 5

SELECT * FROM my_table
WHERE DATE(created_at) >= SUBDATE(DATE(NOW()), 7)

Answer 6

SELECT * FROM my_table WHERE my_table.datefield > sysdate - 7