Re: Time problem again?

Oki, I will check it out....
Thx! :)

BTJ

On Tue, 2003-09-30 at 11:24, Richard Huxton wrote:
> On Tuesday 30 September 2003 09:50, Bjørn T Johansen wrote:
> > On Tue, 2003-09-30 at 10:42, Richard Huxton wrote:
> > > So - your rule is something like:
> > >
> > > For some block of times...
> > > IF max(my_time) - min(my_time) > 12 hours
> > > THEN sort "through midnight"
> > > ELSE sort "naturally"
> > >
> > > Which is what Tom said (I was wondering where he got his 12 hours from).
> > > Have I got that right?
> >
> > Yes, that sounds about right.. :)
> > But how do I code this in an Select statement?
>
> Well, I'd write a function (notice the double-quoting):
>
> -- sort_times(TARGET-TIME, DIFFERENCE)
> -- Takes a target time and the difference max(t)-min(t) in its group
> -- Returns a timestamp you can sort on
> --
> CREATE FUNCTION sort_times(time, interval)
> RETURNS timestamptz AS '
> SELECT
> CASE
> WHEN $2 > ''12:00''::interval AND $1<=''12:00:00''::time
> THEN ''1970-01-02 00:00:00+00''::timestamptz + $1
> ELSE ''1970-01-01 00:00:00+00''::timestamptz + $1
> END
> ' LANGUAGE 'SQL' IMMUTABLE;
>
> Then you have the wrong way:
>
> SELECT
> id, grp, ts
> FROM
> timetest
> ORDER BY
> grp, ts
> ;
>
> id | grp | ts
> ----+-----+----------
> 1 | a | 11:00:00
> 2 | a | 14:00:00
> 3 | a | 17:00:00
> 4 | a | 20:00:00
> 7 | b | 01:00:00 ***
> 8 | b | 04:00:00 *** Oops - these are not
> 5 | b | 20:00:00 *** what we wanted
> 6 | b | 22:00:00 ***
> 9 | c | 03:00:00
> 10 | c | 06:00:00
> 11 | c | 08:00:00
> (11 rows)
>
> And the right way:
> SELECT
> t.id, t.grp, t.ts
> FROM
> timetest t,
> (SELECT grp, max(ts)-min(ts) as tdiff FROM timetest GROUP BY grp) AS diffs
> WHERE
> t.grp = diffs.grp
> ORDER BY
> t.grp, sort_times(t.ts, diffs.tdiff)
> ;
>
> id | grp | ts
> ----+-----+----------
> 1 | a | 11:00:00
> 2 | a | 14:00:00
> 3 | a | 17:00:00
> 4 | a | 20:00:00
> 5 | b | 20:00:00 ***
> 6 | b | 22:00:00 *** Ah - better!
> 7 | b | 01:00:00 ***
> 8 | b | 04:00:00 ***
> 9 | c | 03:00:00
> 10 | c | 06:00:00
> 11 | c | 08:00:00
> (11 rows)
>
> I'm not sure how PG will optimise the correctly sorted one - you'll have to
> try it on your real data and see.