CODEWITHSUNDEEP
mysql
sark9012
sark9012

Reputation: 5727

SQL WHERE on now() not working correctly?

I have the following query....

SELECT fixture_date FROM tbl_foot_games WHERE fixture_date < now() AND leagueid = '68' AND (type = '2' OR type = '12' OR type = '22' OR type = '32') GROUP BY fixture_date ORDER BY fixture_date

This is outputting the following....

1318876658 1319135858 1319395058 1319654258 1319913458 1320176258 1320435458 1320694658 1320953858 1321213058 1321472258

All of these timestamps are ahead of right now. Any idea why it is outputting anything?

I do however, need to change the now() to 3 days in the future. 72 hours!

Any ideas? Thanks :)

Upvotes: 3

Views: 4132

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Try:

SELECT fixture_date 
FROM tbl_foot_games 
WHERE FROM_UNIXTIME(fixture_date) < NOW() 
  AND leagueid = '68' 
AND type IN ('2', '12', '22', '32') 
GROUP BY fixture_date 
ORDER BY fixture_date

But you should really not store timestamps as CHAR or VARCHAR.

Upvotes: 3

oezi
oezi

Reputation: 51797

change your query to:

SELECT NOW(), fixture_date FROM tbl_foot_games ...

to see what NOW() returns. your query seems to be correct (the GROUP BY looks a bit senseless to me...). to ask for a future date, use date_add like this:

... WHERE fixture_date < DATE_ADD(NOW(), INTERVAL 3 DAY) ...

Upvotes: 1

Related Questions