How to sort this?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
klevis miho
Forum Contributor
Posts: 413
Joined: Wed Oct 29, 2008 2:59 pm
Location: Albania
Contact:

How to sort this?

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to sort this?

Post 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()
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: How to sort this?

Post 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);
klevis miho
Forum Contributor
Posts: 413
Joined: Wed Oct 29, 2008 2:59 pm
Location: Albania
Contact:

Re: How to sort this?

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to sort this?

Post by VladSun »

Use Apollo's suggestion and put it in the DB domain:
[sql]SELECT * FROM `mytable` ORDER BY `hour` - INTERVAL 8 HOUR[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
klevis miho
Forum Contributor
Posts: 413
Joined: Wed Oct 29, 2008 2:59 pm
Location: Albania
Contact:

Re: How to sort this?

Post by klevis miho »

VladSun, this query displays me this:

06:00
21:10
20:30
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to sort this?

Post by VladSun »

Show me your query and the structure -
[sql]DESCRIBE `mytable`[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to sort this?

Post 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 :(
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply