#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0

    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.
    TK
  2. #2
  3. No Profile Picture
    Brony & F/OSS Advocate
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2003
    Location
    Anaheim, CA (USA)
    Posts
    6,656
    Rep Power
    2476
    for in the last 3 months (90 days) try the following WHERE clause:
    Code:
    SELECT * FROM `yourTable` WHERE (TO_DAYS(NOW())-TO_DAYS(`timestampColumn`))<=90
    ~~ Peter ~~ :: ( Who am I? ) :: ( Peter's Musings: Uploading myself, bit by bit... ) :: ( Electronic Frontier Foundation ) :: ( I'm a GNU/Linux addict and Free Software Advocate. ) :: ( How to Ask Questions the Smart Way ) :: ( The Fedora Project, sponsored by Red Hat ) :: ( GNOME: The Free Software Desktop Project ) :: ( GnuPG Public Key ) :: ( About me on the WIki )
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    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?
    Thanks.
    TK
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    alternatively, this calculation, which applies no function to the timestamp column, thus allowing an index on the column to be used --
    Code:
    select id
         , `timestamp`
      from yourtable
     where `timestamp`
        >= date_add(now(), interval -3 month)
    if your column is really called `timestamp` then you will have to use backticks to escape it, since timestamp is a reserved word

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    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
    PHP Code:
    SELECT FROM `threadsWHERE `timestamp` >= date_add(Now(), interval -3 month
    but it did not work. Is there any way to convert this field to a Date datatype?
    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
    TK
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    dude, that is a unix timestamp!

    it equals 2004-02-03 07:49:54

    try this:
    Code:
    select * 
      from threads 
     where from_unixtime(`timestamp`)
        >= date_add(Now(), interval -3 month)
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    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!!
    TK

IMN logo majestic logo threadwatch logo seochat tools logo