Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,484
    Rep Power
    1752
    I may be gravely mistaken here, but - the NOW() function will return a value such as 2008-11-11 12:45:34 - which does not compare favourably to a Unix 'epoch time' timestamp such as "1375318861".

    Not sure what would be the most efficient, but you could try changing either of the values to the other format:
    UNIX_TIMESTAMP(NOW())
    FROM_UNIXTIME(starts) and FROM_UNIXTIME(ends)
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    Originally Posted by SimonJM
    I may be gravely mistaken here, but - the NOW() function will return a value such as 2008-11-11 12:45:34 - which does not compare favourably to a Unix 'epoch time' timestamp such as "1375318861".

    Not sure what would be the most efficient, but you could try changing either of the values to the other format:
    UNIX_TIMESTAMP(NOW())
    FROM_UNIXTIME(starts) and FROM_UNIXTIME(ends)
    I try and I will tell you...

    I can't belive there is no php TIME() equivalent in MySQL...
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by =charlie=
    I can't belive there is no php TIME() equivalent in MySQL...
    please do believe it

    it is called UNIX_TIMESTAMP() --notice, no parameter in the function call

    the start and end columns should be INTEGER and not VARCHAR, but luckily for you, mysql will convert automatically

    please, try this --
    Code:
    ORDER 
        BY CASE WHEN starts < UNIX_TIMESTAMP()
                 AND ends > UNIX_TIMESTAMP() -- in progress 
                THEN 1
                WHEN starts > UNIX_TIMESTAMP() -- future auction 
                THEN 2
                ELSE 3 -- terminate 
            END
         , starts
    as for the LIMIT 6 that i saw, i'm going to pretend i never saw that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0

    Thumbs up


    OK, IT'S WORKING!

    Thanks to all!
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo