Re: Time problem again?

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.

--
  Richard Huxton
  Archonet Ltd