Page 1 of 1

How to sort this?

Posted: Mon Jan 18, 2010 7:00 am
by klevis miho
I have a column filled with different hours of a date.
How can I order by the hour, but start from 08:00 in the morning?

For example:
08:00
09:00
09:43
11:00
22:00
01:00
07:00

Thnx in advance

Re: How to sort this?

Posted: Mon Jan 18, 2010 11:53 pm
by JakeJ
Is this information coming from a database?

If there is no AM or PM attached or if they are not actually being stored in a time stamp format, there is no way to sort them properly.

In any case try using array_sort()

Re: How to sort this?

Posted: Tue Jan 19, 2010 3:38 am
by Apollo
The trick is to shift the times so that 8:00 becomes 0:00, then sort, then shift back.

Code: Select all

$startHour = 8;
foreach($a as &$b) $b = substr('0'.((intval(substr($b,0,2))+(24-$startHour))%24),-2).substr($b,2);
 
asort($a);
 
foreach($a as &$b) $b = substr('0'.((intval(substr($b,0,2))+$startHour)%24),-2).substr($b,2);

Re: How to sort this?

Posted: Tue Jan 19, 2010 4:50 am
by klevis miho
Thanx, the information is comming from a database.

I solved it doing 2 queries from the database, one > 08:00 and one > 00:00 < 08:00.

I know it's not the best solution, but It works

Re: How to sort this?

Posted: Tue Jan 19, 2010 5:03 am
by VladSun
Use Apollo's suggestion and put it in the DB domain:
[sql]SELECT * FROM `mytable` ORDER BY `hour` - INTERVAL 8 HOUR[/sql]

Re: How to sort this?

Posted: Tue Jan 19, 2010 5:18 am
by klevis miho
VladSun, this query displays me this:

06:00
21:10
20:30

Re: How to sort this?

Posted: Tue Jan 19, 2010 5:23 am
by VladSun
Show me your query and the structure -
[sql]DESCRIBE `mytable`[/sql]

Re: How to sort this?

Posted: Tue Jan 19, 2010 5:38 am
by VladSun
I think it should work:
[sql]SELECT * FROM mytable ORDER BY CONCAT('1999-01-01 ', `hour`) - INTERVAL 8 HOUR[/sql]

Though, I expected `hour` - INTERVAL 8 to return always positive values :(