## Re: Time problem again?

From: Bjørn T Johansen Richard Huxton Postgres General Re: Time problem again? 2003-09-30 09:54:37 1064915676.20427.18.camel@dt-btj.dagbladet.no (view raw or whole thread) 2003-09-29 09:11:55 from Bjørn T Johansen  2003-09-29 11:21:33 from Richard Huxton   2003-09-29 11:26:41 from Bjørn T Johansen    2003-09-29 11:40:25 from Richard Huxton     2003-09-29 11:47:36 from Bjørn T Johansen      2003-09-29 13:22:23 from Richard Huxton       2003-09-29 19:19:42 from Bjørn T Johansen        2003-09-29 19:38:25 from Tom Lane         2003-09-29 20:33:42 from Bjørn T Johansen        2003-09-29 19:53:05 from Richard Huxton         2003-09-29 20:31:31 from Bjørn T Johansen          2003-09-30 07:40:00 from Richard Huxton           2003-09-30 08:00:25 from Bjørn T Johansen            2003-09-30 08:42:39 from Richard Huxton             2003-09-30 08:50:21 from Bjørn T Johansen              2003-09-30 09:24:53 from Richard Huxton               2003-09-30 09:54:37 from Bjørn T Johansen            2003-09-30 09:13:04 from Karsten Hilbert  2003-09-29 11:44:48 from "Daniel Schuchardt"   2003-09-29 11:49:16 from Bjørn T Johansen pgsql-general
```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.

```

### pgsql-general by date

 Next: From: strk Date: 2003-09-30 10:21:30 Subject: Re: [postgis-users] Union as an aggregate Previous: From: Richard Huxton Date: 2003-09-30 09:24:53 Subject: Re: Time problem again?