Date sorting in MySQL

I am capturing dates in the following format:

Mon, February 14, 2011, 08:22:34 AM

When I sort posts by the most recent date/time by using ORDER BY date_time DESC, sometimes it sorts it correctly and sometimes it doesn't. So is there a better way to capture data so that the sorting will be done correctly?

For instance, w开发者_如何学Cork ten entries in a row it might sort properly, that is it will show the most recent posts first. Then after that it might put the most recent one at the very bottom

Per discussion on OP, you are storing the date as a string. MySQL has many date sorting functions that can sort accurately, but the must be used in conjunction with a DATE type field. You should either convert the field type to date/datetime, or perform a cast function to turn them into date types for the purpose of sorting. Like this:

SELECT CAST(datefield as date) AS date_format ORDER BY date_format DESC

You should be using a DATETIME field instead of a VARCHAR field for storing dates...

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

This will allow you to sort correctly as well as use MySQL datetime functions in queries on your datetime fields.

Based on you saying that the field is a varchar, I assume that you are getting an ASCII sort of the strings in the field. When the sort order is not as you expect, check for things like upper and lower case, etc.

If you want to sort dates it is best to use a datatime datatype.

Are you capturing the date in that format, or using some function like time()? If you sort by a date that is composed by both characters and numbers instead of only numeric-incrementing value, it will bring problems when sorting the entries.

Please correct me if I understood you wrong.

edit: if you want to avoid extra load on the server, you can create a varchar field which contains the processed date (the one you have right now), and a new int one you can use for saving the time(); and sorting the entries by date.