slinky — 2012-08-16T13:34:38-04:00 — #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.
r937 — 2012-08-16T14:42:30-04:00 — #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)
slinky — 2012-08-16T15:11:26-04:00 — #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.
r937 — 2012-08-16T15:40:54-04:00 — #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
mittineague — 2014-09-20T03:02:30-04:00 — #5
This topic is now archived. It is frozen and cannot be changed in any way.