SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2001
    Location
    New York
    Posts
    499
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL doesn't return proper results

    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.
    My site for law answers and legal information -- I also buy websites you may wish to sell

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by slinky View Post
    The database uses a unix time stamp.
    yup, you won't get the right results comparing integers like 1313520063 with datetime values like 2011-08-16

    change this --
    Code:
    WHERE dateline < (NOW() - INTERVAL 12 MONTH)
    to this --
    Code:
    WHERE dateline < UNIX_TIMESTAMP(NOW() - INTERVAL 12 MONTH)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2001
    Location
    New York
    Posts
    499
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    My site for law answers and legal information -- I also buy websites you may wish to sell

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •