MySQL选择（Varchar）时间

Question

I'm managing the date and time in my database. In the backend/PhpMyAdmin, its column type is Varchar.

It is old and and not correctly made system, but it's really huge and changing the column type to date or integer would cost a lot of time.

My question is: How to select ordering by time_column in 00:00 format (g:i)?

It looks like that (works fine but problem is with the time lower than 10 h 9:00, 8:00)

mysql_query("SELECT * FROM l01vs_database ORDER BY time ASC");

I tried

mysql_query("SELECT * FROM l01vs_database ORDER BY STR_TO_DATE(time,'%g:%i') ASC");

But I am not ever sure if it is correct.

Answer 1

I think you are using the wrong syntax variables. You are using '%g:%i', but shouldn't this be '%H:%i'?

"SELECT * FROM l01vs_database ORDER BY STR_TO_DATE(time,'%H:%i') ASC"

If you have the twelve hour format, you can use '%h'. Take a look at this page for more information: http://www.w3cyberlearnings.com/Mysql_STR_TO_DATE