Re: Time problem again?

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

--
Richard Huxton
Archonet Ltd