Mysql order by...

[Nahiyan]

Hello,

I want to fetch data from a MySQL database's table and order the selected data by date.
Which data format is ordered most precisely by MySQL? I also want the date format to be unique. Please express the format just like PHP's one, example: h:i:s.

Thanks,
Nahiyan

[simplypixie]

To fetch any data in a specific order from a database table you just need to use the ORDER BY sytax. So in your example:

"SELECT * FROM tablename ORDER BY date ASC"

Replace tablename with the name of your table, date to the name of your date field and use ASC to retrieve in the order of oldest date first or DESC for most recent first.

You don't say what format you want the date to display in so I can't answer that for you specifically, however if you want something like day/month/year your PHP would be:

echo date('dd/mm/Y', $dateresult);

Replace $dateresult with your specific date result variable.

Hope this helps.

[Nahiyan]

Sorry but I wasn't talking about how to use 'Order By' clause in SQL.
My question was how to use the 'Order By' clause to work with date/time 100% accurately. Normally, it doesn't work properly as MySQL cannot recognize which one is month, hour, day, etc

[diafol]

How do you store your dates?
Using a unix timestamp or unix datetime (yyyy-mm-dd H:i:s) should prove to be consistent. I can't see why there should be a problem - unless you're storing dates in a non-standard format.

[Nahiyan]

My format is not standard, I apologize for the lack of information in my post.

So here is my format : "M d, Y h:i A"

Thanks a lot, I will try to use the UNIX DateTime format.

[simplypixie]

I understand now - I don't have an answer for you with regard to your format but why don't you use the mysql date formats available as they will then store all your date/time information correctly so you will be able to ORDER BY them and then just re-format your dates when you echo out the dates on the front end?

It makes no sense to me to store your dates and times in a custom format when you don't need to and subsequently cause problems in ordering as you are having.

[diafol]

Another solution would be to use MySQL's STR_TO_DATE() function:

SELECT DATE_FORMAT(STR_TO_DATE(datefield, '%M %d, %Y %h:%i %p'),'%Y-%m-%d %H:%i:%s') AS fdate FROM datetable ORDER BY fdate

The date_format is probably overkill though. This could be used to UPDATE your weird dates to standard ones:

UPDATE datetable SET datefield = DATE_FORMAT(STR_TO_DATE(datefield, '%M %d, %Y %h:%i %p'),'%Y-%m-%d %H:%i:%s')

Once you've done that and changed your data input format, you can just use ORDER BY datefield and all will be good.