DATE_SUB returning incorrect rows

[YBH305]

Hey,

I have a table which I'm selecting based on a date_sub:

SELECT private_messages.toID,
                        private_messages.fromID,
                        private_messages.currentDate,
                        private_messages.type,
                        customers.name,
                        customers.lname
                 FROM private_messages
                 INNER JOIN customers ON customers.id = private_messages.fromID
                 WHERE toID =70
                 AND private_messages.currentDate < DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 5 DAY)
                 AND private_messages.currentDate > DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 6 DAY

Which returns:

70 167 2009-12-30 09:21:20 PM Ben L
70 102 2009-12-30 10:20:01 PM Jay Esco
70 102 2009-12-30 10:37:09 PM Jay Esco
70 102 2009-12-30 10:37:15 PM Jay Esco
70 102 2009-12-30 10:37:17 PM Jay Esco
70 102 2009-12-30 10:37:53 PM Jay Esco

When I change the INTERVAL to

AND private_messages.currentDate < DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 4 DAY)
AND private_messages.currentDate > DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 5 DAY

It returns

70 167 2009-12-30 09:21:20 PM Ben L

Which is also part of the 5 - 6 INTERVAL. So in essence its duplicating the data. What I'm trying to accomplish is to grab from a certain day and only that day (nothing before nothing afterwards).

I'm not sure where I'm going wrong?

[YBH305]

I also tried

WHERE currentDate
BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 1 DAY

It always returns zero rows, no matter what interval I assign to it.

[r937]

I also tried

WHERE currentDate
BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 1 DAY

It always returns zero rows, no matter what interval I assign to it.

that's correct, this will ~always~ return zero rows

the reason is because of how BETWEEN works

when you use BETWEEN, you ~must~ put the lower value first

here's how it works...

today is 2010-01-05, so yesterday is 2010-01-04

your BETWEEN statement would be --

WHERE currentDate BETWEEN '2010-01-05' AND '2010-01-04'

BETWEEN is always evaluated as two greater/less-than-or-equal comparisons, so this is equivalent to --

WHERE currentDate >= '2010-01-05' 
  AND currentDate <= '2010-01-04'

and it's impossible for a single currentDate value to be both >= 5th and <= 4th at the same time!!

make sense now?

[crmalibu]

    private_messages.currentDate < CURRENT_DATE - INTERVAL 5 DAY
AND private_messages.currentDate >= CURRENT_DATE - INTERVAL 6 DAY

Important things here are being aware of the difference between date and datetime types, and properly bounding the comparison.

For example, do
select DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 4 DAY)

Notice the time component. Now think about what result that would give. It would change depending on the time of day that you issue the query.

[YBH305]

Sorry for the late response I wasnt able to log in for some time.

Yes I get it, thanks