MySql PHP sort by personalized time value

Question

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

What is "personalized time value"? What is the type of the field hora? — Nedret Recep
– Nedret Recep, Commented Jul 10, 2013 at 16:15
So what datatype is hora? My guess is a varchar, and your +0 is casting it to a numeric where it will only use the numeric value up to the first non-numeric character, so '3:35 PM' will be treated as 3+0, '3:00 PM' will be treated as 3+0 (so they are all treated identically) — Mark Baker
– Mark Baker, Commented Jul 10, 2013 at 16:15
What I mean by "personalized" is that this is not a time type but a string — Lalo Oceja
– Lalo Oceja, Commented Jul 10, 2013 at 16:28

Community · Accepted Answer · 2017-05-23 12:05:28Z

1

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)?

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jul 10, 2013 at 16:16

Rohan Kumar

40.7k11 gold badges81 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nedret Recep Over a year ago

It is better to keep any data of type TIME in a filed of type TIME. That are the different data formats exist in DB. And then use all the nice date/time functions. Any other solution is tricky and may cause future problems.

Marc B · Accepted Answer · 2013-07-10 16:17:23Z

0

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.

answered Jul 10, 2013 at 16:17

Marc B

362k44 gold badges433 silver badges508 bronze badges

6 Comments

Lalo Oceja Over a year ago

Yes, but if I order by hora like you say, I get this:

Lalo Oceja Over a year ago

10:00 AM 10:30 AM 7:00 AM 8:30 AM etc.. and I expected 7:00 AM 8:30 AM 10:00 AM 10:30 AM, how do I achieve this?

Marc B Over a year ago

what else do you expect when you're sorting strings? 1 is less than 7. If you want actual time-ordering, then you'll have to use an actual datetime/time field type, in which case 10:00 does come AFTER 7:00.

Lalo Oceja Over a year ago

yes, that's exactly what I needed: to sort a string like if it was a time type, but now I know how to ask it and achieve it... thank you anyways

Marc B Over a year ago

then don't save it as a string. save it as a real mysql time value. Worst case, you can kill performance and dynamically convert to a native time value with ORDER BY STR_TO_DATE(...).

|

Collectives™ on Stack Overflow

MySql PHP sort by personalized time value

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related