Query trouble - working with dates & intervals

[amites]

Hello,

 

I've got a small chunk of code that is refusing to behave and I'm hoping a fresh pair of eyes will help

 

right now I have

 

SELECT s.id AS msg_id, UNIX_TIMESTAMP(s.msg_date) AS msg_date, u.id AS userid, loc.name AS loc_name, l.id AS look_id
FROM bil_msg_sent AS s
JOIN bil_msg_look AS l ON (l.locid = s.locid)
JOIN bil_users AS u ON (u.id = s.userid)
JOIN bil_location AS loc ON (l.locid = loc.id)
WHERE s.msg_date BETWEEN l.look_date - INTERVAL 1 hour AND l.look_date + INTERVAL 1 hour
AND l.id = '3'
AND s.active = 1
AND l.active = 1
ORDER BY s.msg_date ASC

 

when I take out

WHERE s.msg_date BETWEEN l.look_date - INTERVAL 1 hour AND l.look_date + INTERVAL 1 hour

 

I get 5 results,

 

when I put it in I get none,

 

I've gone in and manually matched the dates in the s.msg_date column to that of l.look_date in those 5 entries though I'm still getting nada for results

 

any fresh perspectives?

 

head is beginning to get sore and think I might need to replace this keyboard 

[fenway]

Remove the where clause, add each piece the the select column output, you'll be suprised.

[amites]

I suppose I should have mentioned that I've already torn this query apart and started fresh piece by piece,

 

everything works property until I add in the the WHERE with the Interval,

 

I have a similar query with the same where clause working in a different page...

 

suppose I'll get back to the proverbial drawing board

[fenway]

By all means... but I like to debug these things by testing the boolean output of each condition independently.

[amites]

funny,

 

I tried writing and re-writing this query a dozen times and couldn't figure it out,

 

then I went back and checked the data itself....

 

I had missed setting one of the fields to active = 1 from active = null

 

 

now where can I find a dunce cap?

 

 

also any reason why I can't mark this as solved?

have something to do with the forum upgrade yesterday?