CODEWITHSUNDEEP
phpmysqltime
user2826311
user2826311

Reputation: 74

MySQL Select by (Varchar) Time

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.

Upvotes: 0

Views: 97

Answers (2)

Benz
Benz

Reputation: 2335

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

Upvotes: 1

m.pons
m.pons

Reputation: 140

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"

Upvotes: 0

Related Questions