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