February 2nd, 2004, 09:54 PM
Compare dates in WHERE clause
I am trying to pull only certain records from my table based on their age. The table structure which I inherited has a field named "timestamp" whose data type is INT and lenght is 12. When we need the dates to show on our web pages it is no problem using php to format them, but I have tried everything I can think of and everything, including all different types of the DATE_FORMAT function, which I found on the Net to creat a comparison in the WHERE clause of one of my queries so we only get certain records.
What I want is only records which are within the last 3 months.
I just joined this forum and I hope someone here can help me.
While I'm on it, is there a way to change that date type to Date without losing the existing data? Or can I create a new field of type Date and then use and Update query to convert the current "timestamp" field data to the Date?
Thanks in advance.
February 2nd, 2004, 11:03 PM
for in the last 3 months (90 days) try the following WHERE clause:
SELECT * FROM `yourTable` WHERE (TO_DAYS(NOW())-TO_DAYS(`timestampColumn`))<=90
February 3rd, 2004, 05:02 AM
I think I tried that. I am assuming that by 'timestampColumn' you are talking about something like timestamp(4) or timestamp(10) or whatever. Is that right?
If it is, I just get an error that timestamp(4) is an unknown column.
So, if I'm missing the boat here altogether, can you help me by giving me the exact syntax. Do I need to but the same number in the parenthesis that is set in the table for the limit of characters - 11?
February 3rd, 2004, 06:33 AM
alternatively, this calculation, which applies no function to the timestamp column, thus allowing an index on the column to be used --
if your column is really called `timestamp` then you will have to use backticks to escape it, since timestamp is a reserved word
>= date_add(now(), interval -3 month)
and of course, it has to be a DATETIME or TIMESTAMP data type -- if it's really an INT field (which i doubt) then the whole thing will likely not work
February 3rd, 2004, 10:59 AM
Thanks for the response. I just double checked and the field name is indeed "timestamp" with a data type of INT and the length set to 12. As I stated before I inherited this database and I just found another table which has the exact same field set up (timestamp, INT, 12). The tables are used for discussion forum tables, one holds the threads - id and "timestamp" and whether they should be displayed or not, the other is holding the actual questions and responses.
I tried the following code
but it did not work. Is there any way to convert this field to a Date datatype?
SELECT * FROM `threads` WHERE `timestamp` >= date_add(Now(), interval -3 month)
If I had designed the table, being an Access SQL person, I would have used the Date type even though I have read a lot of guys who prefer the Unix timestamp. I find it very easy to use for exactly the type of thing I am trying to do now.
Anyway, any help you can give will be greatly appreciated.
Here is an example of the data in the timestamp field of a recently posted question - 1075816194
February 3rd, 2004, 11:30 AM
dude, that is a unix timestamp!
it equals 2004-02-03 07:49:54
>= date_add(Now(), interval -3 month)
February 4th, 2004, 08:26 AM
Yeah, I knew it was treating it like a timestamp, but I wasn't sure why. Is it that a datatype of INT is essentially the same as TIMESTAMP - and if so, why would anyone need the TIMESTAMP data type, why not just always use INT?
Anyway, your latest idea worked great!
Thanks for all your help!!