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