Re: Time problem again?

The problem is that I don't have such a "period". I can have a select containing these data:

2350
0110
0330

which then should be sorted like that.

And I can also have the following:

1030
1145
1240

(also sorted as shown...)

the only thing I know for sure, is that the interval between the first record and the last, is always less than 24 hours...

BTJ

On Mon, 2003-09-29 at 13:21, Richard Huxton wrote:

On Monday 29 September 2003 10:11, Bjørn T Johansen wrote:
> I need to sort some data based on a Time field and the times can cross
> both midnight and noon. As far as I can tell, there is no way to solve
> this without also supplying a date or am I missing something?

You don't say when your "period" starts. This puts me in the same position as 
PostgreSQL - I can't tell you whether 03:00 represents an early or a late 
time in your period.

I presume you have a situation where a period starts at e.g. 06:00:00 and 
continues until 05:59:59 the following day.

You could do something like:

SELECT my_time, CASE   WHEN my_time < '06:00:00' THEN my_time+'18 hours'::interval   ELSE my_time - '6 hours'::interval END AS sort_time
FROM time_table
ORDER BY sort_time

That would translate: my_time       sort_time 06:00:00  =>  00:00:00 07:00:00  =>  01:00:00 00:00:00  =>  18:00:00 05:59:59  =>  23:59:59

You could wrap that up in an SQL function if you wanted, or even add an index 
on the function (but check the manual for details how).