Terungwa Terungwa - 1 month ago 7
MySQL Question

WHERE returning too many rows for data NOT BETWEEN two dates

I want to return data that is not between current date and the last 7 days.

My SELECT statement appears ok, but it is also returning the current day's data.

SELECT
customer.id AS id,
customer.customer_id AS customer_id,
customer.name AS name,
customer.phone1 AS phone1,
customer.location_area AS location_area,
sales.post_date AS post_date
FROM
sales
INNER JOIN
customer
ON
sales.customer_id = customer.customer_id
WHERE
post_date
NOT BETWEEN
CAST( DATE_SUB(NOW(), INTERVAL 7 DAY) AS DATE )
AND
CAST( NOW() AS DATE )
ORDER BY
sales.id
DESC
LIMIT 30


Please note the customer_id field used in the ON clause is not a primary key in any of the two referenced tables.

What might be missing in my query?

Answer

This problem is usually confusion about the different meanings of DATE datatypes on the one hand and TIMESTAMP or DATETIME data types on the other.

Let's say NOW() is 1-April-2017 09:35. And, let's say you have a row in your sales table with a post_date value of 1-April-2017 08:20. Let's say your post_date column has the data type DATETIME.

Then your WHERE clause looks like this after values are applied.

WHERE '2017-04-01 08:20' NOT BETWEEN CAST( '2017-03-25 09:35' AS DATE )
                                 AND CAST( '2017-04-01 09:35' AS DATE )

Applying the CAST operations, we get.

WHERE '2017-04-01 08:20' NOT BETWEEN '2017-03-25'
                                 AND '2017-04-01'

Finally, when comparing a DATETIME or TIMESTAMP to a DATE value, the DATE value is interpreted as having a time of midnight. So your query looks like this:

WHERE '2017-04-01 08:20' NOT BETWEEN '2017-03-25 00:00:00'
                                 AND '2017-04-01 00:00:00'

And, guess what? '2017-04-01 08:20' is after '2017-04-01 00:00:00'.

What you need is this:

WHERE 
 NOT (
            post_date >= CURDATE() - INTERVAL 7 DAY --on or after midnight 2016-3-25
        AND post_date <  CURDATE() + INTERVAL 1 DAY --before midnight 2016-04-02
     )

Please notice that this expression encompasses eight days total.

You can't use BETWEEN for this kind of comparison because you need < for the end of the range, and BETWEEN uses <= for the ends of all its ranges.

Also, CURDATE() is much easier to read than CAST(NOW() AS DATE).

Comments