Time problem again?

[Richard Huxton]

Re: Time problem again?

On Tuesday 30 September 2003 09:00, Bjørn T Johansen wrote:[color=blue]
> Why can't you just take my word for it, this is the way it should be
> sorted....[/color]

I'm happy to take your word, but until I can figure out what rules you're
using I can't suggest anything. You clearly know what you want, but I can't
get to grips with precisely what that is.
[color=blue]
> The reason I know this, is because the timespan, as I have mention
> before, from first record to last record is always less than 12 hours.
> i.e the span from 0200 to 2200 is 20 hours, i.e. 2200 comes before 0200![/color]

Ah - you said 24 hours originally, which means that all the examples we've
looked at were valid. Apologies if I missed the 12-hour bit.

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?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bjørn T Johansen]

Re: Time problem again?

On Tue, 2003-09-30 at 10:42, Richard Huxton wrote:
[color=blue]
> 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?[/color]

Yes, that sounds about right.. :)
But how do I code this in an Select statement?


BTJ


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Karsten Hilbert]

Re: Time problem again?

> Why can't you just take my word for it, this is the way it should be[color=blue]
> sorted....[/color]
He *does* take your word that this is the way it should be
sorted. But without knowing WHY this is the way it should be
sorted it is hard to deduce an algorithm for doing so.

What you probably need to do is sort them lowest -> highest
and then slide a window across the range (and wrapping the
lower times that leave the window) until the difference
between the then-first and then-last time is minimized. Even
then you can't know for sure unless you have additional
knowledge.

data 2 22 4 23

-> 2 4 22 23 -> delta-t 21 hours -> wrong order
-> 4 22 23 2 -> delta-t 22 hours -> wrong order
-> 22 23 2 4 -> delta-t 6 hours -> candidate
-> 23 2 4 22 -> delta-t 23 hours -> wrong order

here you can find your sort order by (delta-t < 12).

But what if there's another 1300 data point in there ?

2 4 13 22 23 21
4 13 22 23 2 22
13 22 23 2 4 15
22 23 2 4 13 15
23 2 4 13 22 23

I assume you are telling us that won't happen, right, i.e. it
is one of the rules ?

And what do you make of this sequence:

data 22 10

-> 10 22 12 -> candidate
-> 22 10 12 -> candidate

You't want to make sure delta-t is LESS than 12 hours.

I am sure there's an efficient algorithm out there to do this.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Richard Huxton]

Re: Time problem again?

On Tuesday 30 September 2003 09:50, Bjørn T Johansen wrote:[color=blue]
> On Tue, 2003-09-30 at 10:42, Richard Huxton wrote:[color=green]
> > 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?[/color]
>
> Yes, that sounds about right.. :)
> But how do I code this in an Select statement?[/color]

Well, I'd write a function (notice the double-quoting):

-- sort_times(TARG ET-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''::inte rval 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bjørn T Johansen]

Re: Time problem again?

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


BTJ

On Tue, 2003-09-30 at 11:24, Richard Huxton wrote:[color=blue]
> On Tuesday 30 September 2003 09:50, Bjørn T Johansen wrote:[color=green]
> > On Tue, 2003-09-30 at 10:42, Richard Huxton wrote:[color=darkred]
> > > 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?[/color]
> >
> > Yes, that sounds about right.. :)
> > But how do I code this in an Select statement?[/color]
>
> Well, I'd write a function (notice the double-quoting):
>
> -- sort_times(TARG ET-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''::inte rval 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.[/color]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend