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.
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
Depending on wether the date was entered as 24h format or not, you might want to change the STR_TO_DATE(time,'%g:%i') to STR_TO_DATE(time,'%H:%i'). You'll have the leading zeros and that way you won't have problems with the 9:00 and 8:00. If it was entered as 12h format, then use the lower "h"