Sort date in mysql

Question

I collect dates in the following format:

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

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

For example, make ten entries in a row that it can sort correctly, that is, the last messages will be displayed first. Then after that he can put the very last one at the very bottom.

Answer 1

During the discussion of the OP, you save the date as a string. MySQL has many date sorting functions that can be sorted exactly, but they must be used in conjunction with a field of type DATE. You must either convert the field type to date / datetime, or execute the cast function to turn them into date types for sorting purposes. Like this:

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

Answer 2

You should use the DATETIME field instead of the VARCHAR field to store dates ...

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

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

Answer 3

Do you write the date in this format or use any function, for example time ()? If you sort by date, which consists of two characters and numbers, and not just a numerical increment, this will lead to problems when sorting records.

Please correct me if I understand that you are mistaken.

edit: if you want to avoid additional server load, you can create a varchar field that contains the processed date (the one you have now) and a new int that you can use to save time (); and sorting entries by date.

Answer 4

Based on the fact that the field is varchar, I assume that you get the ASCII type of strings in the field. If the sort order is not what you expect, check things like upper and lower case, etc.

If you want to sort dates, it is better to use the datatime data type.