MYSQL doesn't return proper results

Databases
1

I have a strange problem with a v bulletn forum that I run. I’m trying to select all threads that are older than a certain date. I looked up the mysql syntax for the query and got:

SELECT * FROM thread WHERE dateline < (NOW() - INTERVAL 12 MONTH)

I could use any interval and I get the same result of only one record where there isn’t any date inserted, just a zero. The database uses a unix time stamp. Trying every variation doesn’t seem to return results. In the above query, thread is a table with a bunch of fields and dateline is one of them that has the unix timestamp. I am guessing that perhaps the query doesn’t work with unix timestamps and perhaps must be converted into a different format in order to make use of the mysql functions.

2

yup, you won’t get the right results comparing integers like 1313520063 with datetime values like 2011-08-16

change this –

WHERE dateline < (NOW() - INTERVAL 12 MONTH)

to this –

WHERE dateline < UNIX_TIMESTAMP(NOW() - INTERVAL 12 MONTH)
3

Thanks for the reply. I thought I tried the UNIX_TIMESTAMP function but I didn’t get the desired results. What did work was using the FROM_UNIXTIME which, as you explain, essentially converts the integer to the right form. Thanks much for your help.

4

you say you used FROM_UNIXTIME – be aware that if you apply a function to a table column, the query has no choice but to do a table scan even if the column (dateline in this example) has an index

please – try my suggestion again, use copy/paste from post #2