CODEWITHSUNDEEP
mysqlsql
Amrutanshu Panigrahi
Amrutanshu Panigrahi

Reputation: 21

to sort time ( AM / PM ) in SQL?

I am facing problem with sorting according to AM / PM.

Here is my table data

login_time
1:30 PM
11:00 AM
10:00 AM
12:30 PM
10:00 PM
11:10 AM
12:20 PM
3:40 PM
2:20 PM

I want result as:

10:00 AM
11:00 AM
11:10 AM
12:20 PM
12:30 PM
1:30  PM
2:20 PM
3:30 PM

Upvotes: 2

Views: 1323

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You are not saving datetime data with proper data-type and this will create issues like you are having now. Your data is saved as varchar and they are not proper date or time.

However in your case you may try as below, i.e. first convert it to a real time and then get the seconds and then order by.

select * from table_name
order by time_to_sec(str_to_date(login_time,'%h:%i %p'))

Without time_to_sec function should work too.

 order by str_to_date(login_time,'%h:%i %p')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If login_time is stored as a time, you can just do:

order by login_time

If login_time is stored as a string, you can do:

order by str_to_date(login_time, '%h:%i %p')

Upvotes: 1

Peter Bowers
Peter Bowers

Reputation: 3093

Well, you haven't shown us your SELECT query so I'm taking wild guesses, but I would think you probably have something like this:

SELECT login_time
FROM my_table

and you need to have this:

SELECT login_time
FROM my_table
ORDER BY login_time

That is, of course, assuming that login_time is of the appropriate type time.

Upvotes: 1

Related Questions