How to sort time (in AM / PM) in SQL?

Question

I ran into a sorting problem according to AM / PM.

Here are my table data

login_time 1:30 PM 2:00 AM 8:00 AM 9:30 PM 10:00 PM 11:10 AM 

I want a result like:

 login_time 2:00 AM 8:00 AM 11:10 AM 1:30 PM 9:30 PM 10:00 PM 

How can i do this?

Answer 1

You can use the STR_TO_DATE function, and you can also only extract part of the time with TIME () if you want:

 SELECT login_time FROM tablename ORDER BY STR_TO_DATE(login_time, '%l:%i %p') 

See the fiddle here .

Answer 2

If you already have a VARCHAR date, I suggest you reorganize this into a TIME column. This can be easily done online:

 UPDATE tablename SET login_time = STR_TO_DATE(login_time, '%l:%i %p'); ALTER TABLE tablename CHANGE login_time login_time TIME; 

I am using the TIME type here, since your data apparently only contains time. Maybe a DATETIME type would be most appropriate. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html for various parameters.

In any case, as soon as the column is reorganized to the appropriate type, you can write:

 SELECT login_time FROM tablename ORDER BY login_time 

With the added benefit, you can now create an index on login_time to speed up searching / sorting:

 ALTER TABLE tablename ADD INDEX ( login_time ); 

See http://sqlfiddle.com/#!2/03991/1 (click "View Execution Plan" to see that the actual index has been used).

Answer 3

Just use a few expressions in the ORDER BY clause to sort by AM / PM first, by 12-hour special occasion, third hour and tie-break in minutes for the rest.

 ORDER BY RIGHT(login_time, 2), CASE WHEN 12 = convert(int, REPLACE(LEFT(login_time, 2), ':', '')) THEN 1 ELSE 2 END, convert(int, REPLACE(LEFT(login_time, 2), ':', '')), login_time 

Answer 4

As the comments mentioned, if possible, change the column data type. If this does not match your control order with the converted value:

 select login_time from table order by CAST(login_time as datetime)