SQL WHERE on now() not working correctly?

Question

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 :)

just a minor enhancement you could also do, AND type IN ('2', '12', '22', '32') — bumperbox, Oct 14 '11 at 19:45
I'll amend @bumperbox 's comment to say you _should_ get in the habit of using `IN ()`, as it saves you debugging when you've forgotten to surround the `OR` groups in `()` and you start getting all sorts of unwanted rows. — Michael Berkowski, Oct 14 '11 at 19:48
Ok guys, thanks for the advice...had never heard of this before and as you say Michael, debugging for it is a pain! — sark9012, Oct 14 '11 at 19:51

score 3 · Accepted Answer · answered Oct 14 '11 at 20:08

3

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.

answered Oct 14 '11 at 20:08

ypercubeᵀᴹ

113,259
19
174
235

Yes! That works perfectly, was just searching google for a way to convert the timestamp! Very very much appreciate this :) Cheers bud... – sark9012 Oct 14 '11 at 20:10
And this: http://stackoverflow.com/questions/5989539/when-to-use-datetime-or-timestamp – ypercubeᵀᴹ Oct 14 '11 at 20:13
For the differences `timestamp` vs. `datetime` and what to use. – ypercubeᵀᴹ Oct 14 '11 at 20:13

score 1 · Answer 2 · answered Oct 14 '11 at 19:40

1

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) ...

answered Oct 14 '11 at 19:40

oezi

51,017
10
98
115

@Luke If you need to group repeating rows, only `ORDER BY` is needed. `GROUP BY` is for aggregates like `COUNT(), SUM(), AVG()`, etc. – Michael Berkowski Oct 14 '11 at 19:46
yes good idea. check what NOW() is by selecting it. there could be different time zones then you expect – bumperbox Oct 14 '11 at 19:47
I have several rows with the same fixture_date so I group them? If i remove the group by, I end up with them all displaying individually? And this still isn't working. Those are the timestamps above and even when i go three days in the future, i'm still getting them all display??? – sark9012 Oct 14 '11 at 19:48
@bumperbox - it's showing timestamps till november, the zones couldnt be that far out?? – sark9012 Oct 14 '11 at 19:48
When I echo now(), i get 2011-10-14 21:49:23....how does that compare to a timestamp? Is that the problem? – sark9012 Oct 14 '11 at 19:50
1

is the mysql column a timestamp type or a datetime time? – bumperbox Oct 14 '11 at 19:52
Oooops it's storing a timestamp but it's of varchar type...is there anything i can do about that? – sark9012 Oct 14 '11 at 19:54
instead of NOW(), try UNIX_TIMESTAMP() for comparison. i haven't tried it, but see if that make a difference. mysql timestamps are not the same as unix timestamps – bumperbox Oct 14 '11 at 19:56
Don't think that has worked! It's just made everything disappear. – sark9012 Oct 14 '11 at 20:01

SQL WHERE on now() not working correctly?

2 Answers2