SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Dates are not sorting correctly with date_format

    I have formatted my date/time column using the following, but it is not sorting the results correctly. Does anyone know why?

    date_format(searchDate, '%Y-%m-%d %l:%i %p') as date

    Results from the query:

    2011-10-04 9:53 AM
    2011-10-04 10:38 AM
    2011-10-04 10:34 AM

    Should I sort the data using the query and then format how the date looks using PHP?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    DATE_FORMAT produces a string

    if you sort on that string, and it contains AM/PM, then you get weird results

    go ahead and use DATE_FORMAT on the column to display it the way you want, but in the ORDER BY clause, sort on the unformatted column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately this is a query that combines rows from three different tables. One table calls is searchDate, another table calls is dateAdded, etc. So I think I'm forced to either sort with this string version, or use PHP to format how the date shows.

    What say ye?
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    What say ye?
    not much, because i can't see your actual query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SELECT date_format(answerDate, '%m-%d-%Y %l:%i %p') as date
    , cID AS ID
    , 'campaign' AS source
    , campaign AS type
    , answer AS entry
    FROM campaigns
    WHERE uID = '$uID'
    UNION ALL
    SELECT date_format(searchDate, '%Y-%m-%d %l:%i %p') as date
    , sID as ID
    , 'searches' AS source
    , type
    , keyword
    FROM searches
    WHERE author = '$uID' limit 5
    UNION ALL
    SELECT date_format(dateAdded, '%Y-%m-%d %l:%i %p') as date
    , cID as ID
    , 'communications' AS source
    , type
    , entry
    FROM communications
    WHERE uID = '$uID'
    UNION ALL
    SELECT date_format(payDate, '%Y-%m-%d %l:%i %p') as date
    , paymentID as ID
    , 'payments' AS source
    , type
    , frequency
    FROM payments
    WHERE uID = '$uID'
    ORDER
    BY date desc
    , source;
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hm, that query looks vaguely familiar...
    Code:
    SELECT DATE_FORMAT(answerDate, '%m-%d-%Y %l:%i %p') as date
         , answerDate AS sort_date
         , cID AS ID
         , 'campaign' AS source
         , campaign AS type
         , answer AS entry
      FROM campaigns
     WHERE uID = '$uID'
    UNION ALL
    SELECT DATE_FORMAT(searchDate, '%Y-%m-%d %l:%i %p') as date
         , searchDate AS sort_date
         , sID as ID
         , 'searches' AS source
         , type
         , keyword
      FROM searches
     WHERE author = '$uID' -- limit 5 is invalid here
    UNION ALL
    SELECT DATE_FORMAT(dateAdded, '%Y-%m-%d %l:%i %p') as date
         , dateAdded AS sort_date
         , cID as ID
         , 'communications' AS source
         , type
         , entry
      FROM communications
     WHERE uID = '$uID'
    UNION ALL
    SELECT DATE_FORMAT(payDate, '%Y-%m-%d %l:%i %p') as date
         , payDate AS sort_date
         , paymentID as ID
         , 'payments' AS source
         , type
         , frequency
      FROM payments
     WHERE uID = '$uID'
    ORDER
        BY sort_date DESC
         , source;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Strange, but the limit 5 actually works for me.
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, so what is the best way to limit the number of results from each of the three tables listed in the query above?
    Convert your dollars into silver coins. www.convert2silver.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    Rudy, so what is the best way to limit the number of results from each of the three tables listed in the query above?
    ----> "Strange, but the limit 5 actually works for me." - busboy

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, I guess I need to clarify. Limit five actually limits the results from that table to 5, but I can't do an order by clause, so therefore it's not the 5 most recent searches in the search table. Does that help you understand my situation?
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what if you wrote the query for only one table, and included the ORDER BY clause, along with LIMIT 5

    that would work, right?
    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
  •