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