[RESOLVED] mysql date ordering problem

PureDrive

I have a mysql column name utc_date which holds a date in utc format. The datatype of utc_date is naturally date. What I need is to get all of the dates from this table in descending order. The query I am using is:

$query = "SELECT DISTINCT date FROM report_strength WHERE user_id=" . "'" . $this->user_id . "'" . " ORDER BY utc_date DESC";

So far I only have 5 entries in the report_strength table and this query returns the following list of dates:

12-01-2007, 12-05-2007, 10-18-2007, 01-19-2004, 02-01-2003 in order. This list is clearly not what its supposed to (not what I intended it to be). What do I need to do in order the get the list of dates in descending order? Thanks!

bradgrafelman

What column type are you using? If you're storing dates, it should be a DATE (or possibly a DATETIME) column, that way you can not only use the datetime functions, but MySQL will also correctly handle operations concerning dates (i.e. ordering them).

PureDrive

I am using a column of type DATE.

NogDog

You are selecting the "date" column for output but using the "utc_date" column for the sorting. Is this the desired functionality? If so, should both have the same date, and Is "utc_date" some sort of MySQL date/time column?

PureDrive

utc_date is a DATE type while date is a VARCHAR. Yes, this is the desired functionality. Since DATE types can only be in UTC format I decided to have two date fields, one for sorting purposes (utc_date) and for for outputting purposes (date)

NogDog

It would be better to just use one date type column (more "normalized"), and use the MySQL DATE_FORMAT() function to convert it to a desired format, e.g.:

$query = "SELECT DATE_FORMAT(utc_date, '%m-%d-%Y') AS date FROM report_strength WHERE user_id=" . "'" . $this->user_id . "'" . " ORDER BY utc_date DESC";