SELECT date BETWEEN dates with interval

Question

I have a date column in my database. I use SELECT COUNT to calculate the rows between today and 15 days ago:

SELECT count(date) as date
FROM `inv`
WHERE user_id='2'
AND date BETWEEN CURDATE() - INTERVAL 15 DAY
AND CURDATE()

This SQL statement is working. But now I want use SELECT COUNT to calculate the rows between today(-15 days) and 30 days ago. But I am getting an error when I try the following statement:

SELECT count(date) as date
FROM `inv`
WHERE user_id='2'
AND date BETWEEN date(CURDATE(),INTERVAL -15 day)
AND date(CURDATE(),INTERVAL -30 day)

Also I want to know how I can SELECT the rows where the date is more than 30 days ago. Can someone help me with this?

Answer 1

You can use the below to get rows between 15 to 30 days old.

SELECT count(date) as date
FROM `inv`
WHERE user_id=2
AND date BETWEEN CURDATE() - INTERVAL 30 DAY
             AND CURDATE() - INTERVAL 15 DAY

Similarly you can use below to get rows that are older than 30 days.

SELECT count(date) as date
FROM `inv`
WHERE user_id=2
AND date < CURDATE() - INTERVAL 30 DAY