Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0

    Order by Timestamp but...


    Ciao,

    I'm writing an auction database and the auction_end is Timestamp.

    I need to create a query where I select for first Timestamp <= Now (you can image this information like auction "in progress") after this I needs Timestamp > Now (like "future" auction) and last Timestamp expired.

    Now my query is:

    Code:
    SELECT * FROM auctions WHERE auctions_starts <= " . $NOW . " ORDER BY ends ASC LIMIT 6
    But this give me for first the expired and after the in progress auction.

    Thanks for help...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Code:
    ORDER BY auctions_starts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    ty

    I test it but now I have:

    future auction -- in progress -- terminate

    I need:

    in progress -- future auction -- terminate
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    it appears that you have two columns, `auctions_starts` and `ends`

    is this correct?

    so depending on the relationship of these two columns with the current timestamp (NOW), this is how you determine the three different case -- in progress, future auction, terminate -- correct?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    Yes to all questions, I have "start" and "end" both Timestamp.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Code:
    ORDER 
        BY CASE WHEN starts < NOW()
                 AND ends > NOW() -- in progress 
                THEN 1
                WHEN starts > NOW() -- future auction 
                THEN 2
                ELSE 3 -- terminate 
            END
         , starts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    Ok, I understand the logic but can you explain me why I must use 1, 2 and 3 followed by ", starts" ?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    1, 2, 3 provide the correct sort sequence for the three different categories of results, as per your request

    you could actually use any other three constants, like 'dewey', 'huey', and 'louie'... or 'dumpty', 'humpty' and 'todd'... or...

    within each category, the rows are sequened by start, and if you left this out, the rows would be sequenced randomly within each category
    Last edited by r937; July 7th, 2013 at 06:51 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    I test this:

    Code:
    ORDER BY 
      CASE 
        WHEN starts < NOW() AND ends > NOW() THEN starts < NOW() 
        WHEN starts > NOW() THEN starts > NOW() 
        ELSE ends < NOW() 
      END 
    , starts DESC...
    but it return me the future auction, in progress and the closed...

    I have also tried:

    Code:
    ORDER BY starts < NOW(), starts > NOW(), ends < NOW() DESC...
    with the same result.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by =charlie=
    I test this:but it return me the future auction, in progress and the closed...
    so, obviously, you should go back to the version i gave you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    Originally Posted by r937
    so, obviously, you should go back to the version i gave you

    ehm... your solution give me the same results :

    - future auction
    - in progress auction
    - terminate auction

    This is last part of the query:

    Code:
    ORDER 
        BY CASE WHEN starts < NOW()
                 AND ends > NOW()
                THEN 1
                WHEN starts > NOW() 
                THEN 2
                ELSE 3 
            END
         , starts DESC LIMIT 6";
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    my only conclusions are either

    1. i don't understand your definition of "future", "in progress" etc. and my CASE uses the wrong tests, or

    2. your columns are not what i assumed, so could you please do a SHOW CREATE TABLE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    OK, from the beginning...

    This is the table:

    Code:
    CREATE TABLE IF NOT EXISTS `auctions` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `user` int(32) DEFAULT NULL,
      `title` varchar(70) DEFAULT NULL,
      `subtitle` varchar(70) DEFAULT NULL,
      `starts` varchar(14) DEFAULT NULL,
      `description` text,
      `pict_url` tinytext,
      `category` int(11) DEFAULT NULL,
      `secondcat` int(11) DEFAULT NULL,
      `minimum_bid` double(16,4) DEFAULT '0.0000',
      `shipping_cost` double(16,4) DEFAULT NULL,
      `reserve_price` double(16,4) DEFAULT NULL,
      `buy_now` double(16,4) DEFAULT NULL,
      `auction_type` char(1) DEFAULT NULL,
      `duration` varchar(7) DEFAULT NULL,
      `increment` double(8,4) NOT NULL DEFAULT '0.0000',
      `shipping` char(1) DEFAULT NULL,
      `payment` tinytext,
      `international` char(1) DEFAULT NULL,
      `ends` varchar(14) DEFAULT NULL,
      `current_bid` double(16,4) DEFAULT NULL,
      `closed` char(2) DEFAULT NULL,
      `photo_uploaded` tinyint(1) DEFAULT NULL,
      `quantity` int(11) DEFAULT NULL,
      `suspended` int(1) DEFAULT '0',
      `relist` int(11) NOT NULL DEFAULT '0',
      `relisted` int(11) NOT NULL DEFAULT '0',
      `num_bids` int(11) NOT NULL DEFAULT '0',
      `sold` enum('y','n','s') NOT NULL DEFAULT 'n',
      `shipping_terms` tinytext NOT NULL,
      `bn_only` enum('y','n') NOT NULL DEFAULT 'n',
      `bold` enum('y','n') NOT NULL DEFAULT 'n',
      `highlighted` enum('y','n') NOT NULL DEFAULT 'n',
      `featured` enum('y','n') NOT NULL DEFAULT 'n',
      `current_fee` double(16,4) DEFAULT '0.0000',
      `testimonial` varchar(200) DEFAULT NULL,
      `associazione1` tinytext,
      `percentuale1` int(3) DEFAULT NULL,
      `associazione2` tinytext,
      `percentuale2` int(3) DEFAULT NULL,
      `associazione3` tinytext,
      `percentuale3` int(3) DEFAULT NULL,
      `associazione4` tinytext,
      `percentuale4` int(3) DEFAULT NULL,
      `legal_terms` tinytext,
      `titolostoria` varchar(100) DEFAULT NULL,
      `storia` tinytext,
      `asta_fissa` varchar(2) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `id` (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;
    Auction that starts in future can have the follow date:

    01-08-2013 01:01:01 --> 1375318861 --> starts field
    01-09-2013 01:01:01 --> 1377997261 --> ends field
    (the next august)

    Aution in progress starts before now and ends after now.

    I hope to answer you.

    Ty
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    What, exactly, are you storing as a value in those 'timestamp' fields? And why use varchar when mySQL has a perfectly decent set of date/time column types ?
    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
  28. #15
  29. 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
    What, exactly, are you storing as a value in those 'timestamp' fields? And why use varchar when mySQL has a perfectly decent set of date/time column types ?
    The values are the timestamp:

    1375318861 <--this in starts
    1377997261 <--this in ends

    unfortunately I am modify the work started by another programmer...
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo