CODEWITHSUNDEEP
phpmysqlsortingtime
Lalo Oceja
Lalo Oceja

Reputation: 57

MySql PHP sort by personalized time value

I have a personalized time value in my database which looks like this: 3:35 PM (it never begins with zero), I need to order by time asc, I tried:

SELECT * FROM mytable WHERE(mydate=\"$thedate\") ORDER BY hora+0

I get this:

3:35 PM
3:00 PM
3:12 PM

but I was expected:

3:00 PM
3:12 PM
3:35 PM

any help?

thanks in advance

Upvotes: 0

Views: 200

Answers (2)

Marc B
Marc B

Reputation: 360642

ORDER BY hora+0 is your problem. You're taking your time values, and trying to do an arithmetic addition on them. This destroys the inherent "time" type of the stored time and converts it into a badly mangled integer:

mysql> select curtime(), curtime()+0;
+-----------+---------------+
| curtime() | curtime()+0   |
+-----------+---------------+
| 10:16:19  | 101619.000000 |
+-----------+---------------+

Try just ORDER BY hora, WITHOUT the addition.

Upvotes: 0

Rohan Kumar
Rohan Kumar

Reputation: 40639

Try like,

SELECT * FROM table ORDER BY str_to_date(meta_time,'%l:%i')

You can find the specific formatters on the MySQL Website.

For example:

%k -> Hour (0..23) %l -> Hour (1..12)

Source from MySQL: Order by time (MM:SS)?

Upvotes: 1

Related Questions