Php MySql sort custom order by hours

Question

I have a mysql table with 3 columns "Event" in text format, "Date" in format YYYY-MM-DD and "Hour" in format HH:MM. For example:

Event1  2013-08-20  18:30
Event2  2013-08-20  19:30
Event3  2013-08-20  20:00
Event4  2013-08-20  00:00
Event5  2013-08-20  02:30
Event6  2013-08-20  14:50
Event7  2013-08-20  00:30

The database contains various days and various hours into a day. I have to order all the events first in days and later in hours, but the order of hours have to be like this example:

Event6  2013-08-20  14:50
Event1  2013-08-20  18:30
Event2  2013-08-20  19:30
Event3  2013-08-20  20:00
Event4  2013-08-20  00:00
Event7  2013-08-20  00:30
Event5  2013-08-20  02:30

The hours 00:00, 01:00, 02:00... are normally at the beginning, but I need to change the order. The hours 00:00, 01:00, 02:00... should appear at the end, like you can see in the last example.

My sentence is:

SELECT * FROM Events ORDER BY Date, Hour

But this sentence is not the appropiate for me because it get me back this values:

Event4  2013-08-20  00:00
Event7  2013-08-20  00:30
Event5  2013-08-20  02:30
Event6  2013-08-20  14:50
Event1  2013-08-20  18:30
Event2  2013-08-20  19:30
Event3  2013-08-20  20:00

Which sentence in MySql or PHP need I to show the results like I want?

Thanks for all ;)

Glavić · Accepted Answer · 2013-08-30 10:55:25Z

1

You can use MySQL function TIME_TO_SEC() and IF statement in ORDER BY.

Let's say everything bellow 05:00 should be at the end :

SELECT
   *
FROM
   `Events`
ORDER BY
  `Date` ASC,
  IF(TIME_TO_SEC(`Hour`) < TIME_TO_SEC('05:00'), 1, 0) ASC,
  `Hour` ASC

Example of SQLFIDDLE.

edited Aug 30, 2013 at 10:55

answered Aug 28, 2013 at 19:20

Glavić

43.6k13 gold badges79 silver badges108 bronze badges

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

6 Comments

Glavić Over a year ago

I have put this records into database table, tested it, and it works. Can you paste sql dump for table and records so I can test it on your data? +I have updated the answer with sqlfiddle example

Glavić Over a year ago

I understood that you have DATE field type for date field, and TIME field type for hour? But still, your data is working for me (but DATE sorting will not work)... sqlfiddle.com/#!2/77abc/1 Can you put your not-working example on sqlfiddle.com ?

Glavić Over a year ago

Because you are sorting by string 'Hora', and not field `Hora`. Your fixed sql statement > sqlfiddle.com/#!2/c201a/6 . And change Hora field type to TIME instead of VARCHAR...

Sergio Garrido Over a year ago

Yeeeessss!!! You are the best! I work with Hora (varchar) and your sentence IF(TIME_TO_SEC(Hour) < TIME_TO_SEC('05:00'), 1, 0) ASC, Hour ASC. I was wrong with `'. 1000 Thanks!

Sergio Garrido Over a year ago

with de type TIME in field Hora... the result is January, 01 1970 23:00:00+0000.... 1970??

|

arielcr · Accepted Answer · 2013-08-27 22:52:55Z

0

Have you tried

SELECT * FROM Events ORDER BY Date, time(Hour)

answered Aug 27, 2013 at 22:52

arielcr

1,6632 gold badges23 silver badges34 bronze badges

1 Comment

Sergio Garrido Over a year ago

this solution not work, the result is the same. The column of Hour is in String...

Collectives™ on Stack Overflow

Php MySql sort custom order by hours

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related