How to do : "Between TODAY AND TODAY-7"?

Question

I need to find the account created for the current day, et for the last 7 days.

To find my results for today, it works, and I do this :

SELECT * FROM `account` where DATE(created_at) = DATE(NOW())

But I don't know how to do to get the last 7days account.

I tried something like this, but without success :

SELECT * FROM `account` where DATE(created_at) BETWEEN DATE(NOW()) AND DATE(NOW()-7)

Have you an idea ?

Answer 1

in mysql:

SELECT * FROM `account` 
WHERE DATE(created_at) > (NOW() - INTERVAL 7 DAY)

Answer 2

Try:

BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()

Answer 3

If created_at has an index and you wouldn't like to prevent the optimiser from using it, I would recommend the following pattern (assuming created_at contains both date and time):

WHERE created_at >= CURRENT_DATE - INTERVAL 7 DAY
  AND created_at <  CURRENT_DATE + INTERVAL 1 DAY

This spans the range from the day exactly one week ago till today (inclusive), so 8 days in total.