CODEWITHSUNDEEP
phpmysqlsqldatedatetime
Sergio Garrido
Sergio Garrido

Reputation: 141

Php MySql sort custom order by hours

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

Upvotes: 2

Views: 1189

Answers (2)

Glavić
Glavić

Reputation: 43552

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.

Upvotes: 1

arielcr
arielcr

Reputation: 1663

Have you tried 

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

Upvotes: 0

Related Questions