MySQL - ORDER BY STR_TO_DATE not working

Question

I am trying to run a PDO query with PHP and MySql and then sort it by date.

My query is below:

    $query_params = array( 
        ':website' => $site
    );

    $query = " 
        SELECT 
            DATE_FORMAT(date, '%d/%m/%Y') AS date,
            id
        FROM
            items as bi
        INNER JOIN 
            basket as bb ON bi.item_number=bb.basket_item
        INNER JOIN
            orders as bo ON bb.basket_order=bo.order_number 
        WHERE
            bi.website = :website
        ORDER BY 
            STR_TO_DATE(date,'%d/%m/%Y') DESC
    "; 

    try { 
        $stmt = DB::get()->prepare($query); 
        $stmt->execute($query_params); 
        $rows = $stmt->fetchAll();
    } 
    catch(PDOException $ex) {} 

    foreach($rows as $row):
        $output .= "".$row["date"].",";
        $output .= "".$row["id"].",";
        $output .= "\r\n <br />";
    endforeach;

Where my output should be:

13/06/2014, 8676,
12/06/2014, 5765,
12/04/2014, 7683, 
08/12/2013, 1098, 
06/12/2013, 2003, 
06/12/2013, 6755,

It doesn't seem to be sorting by anything:

12/06/2014, 5765,
12/04/2014, 7683, 
13/06/2014, 8676, 
06/12/2013, 2003, 
06/12/2013, 6755, 
08/12/2013, 1098,

Should STR_TO_DATE(date,'%d/%m/%Y') DESCnot be sorting as intended?

Fluffeh · Accepted Answer · 2014-06-13T10:34:02.143

2

You already have a lovely date column in your table - why on earth try to sort by some formatted string based on that?

    SELECT 
        DATE_FORMAT(date, '%d/%m/%Y') AS date,
        id
    FROM
        items as bi
    INNER JOIN 
        basket as bb ON bi.item_number=bb.basket_item
    INNER JOIN
        orders as bo ON bb.basket_order=bo.order_number 
    WHERE
        bi.website = :website
    ORDER BY 
        date DESC

Sure, format the date output to the user however you like - but you are not only making the DB do a lot more by formatting each row of data then sorting by something that could be done natively by the database the way it was meant to be.

Edit: Interesting. I wonder if the fact that date is a semi-reserved word is causing your sort not to happen as expected?

Maybe try this:

    ORDER BY 
        bo.date DESC

edited Jun 13 '14 at 10:34

answered Jun 13 '14 at 10:09

Fluffeh

33,228
16
67
80

When i use the above it orders my date in desc order but doesn't take the month and year into account so it is only sorting by day. My date field has the type `datetime` with 0000-00-00 00:00:00 (which i cannot change) so my theory was to first strip out the time in the SELECT part and then order at the end – odd_duck Jun 13 '14 at 10:17
@odd_duck Sorting by a date field will most certainly take the day and month into account - it takes EVERYTHING in the field into account. – Fluffeh Jun 13 '14 at 10:22
@Fluffeh strange that mine is not sorting correctly then. Could the fact my date column is in the `orders` table (the last join) and not in the first `items` table have anything to do with it? – odd_duck Jun 13 '14 at 10:30
Ah spot on with adding `bo.date DESC` - that now sorts as expected, thank you – odd_duck Jun 13 '14 at 10:39
@odd_duck No problems - and really, please if you already have data formatted perfectly (as in a date for example) try to let the db use it to the best of its abilities :) – Fluffeh Jun 13 '14 at 10:40

score 1 · Answer 2 · answered Jun 13 '14 at 10:09

try this

$query = " 
    SELECT 
        DATE_FORMAT(date, '%d/%m/%Y') AS date,
        id
    FROM
        items as bi
    INNER JOIN 
        basket as bb ON bi.item_number=bb.basket_item
    INNER JOIN
        orders as bo ON bb.basket_order=bo.order_number 
    WHERE
        bi.website = :website
    ORDER BY 
        date DESC
";

MySQL - ORDER BY STR_TO_DATE not working

2 Answers2