Time problem again?

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

--
Richard Huxton
Archonet Ltd

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 12:26, Bjørn T Johansen wrote:

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

And how do you know that the first example shouldn't have been
0110
0330
2350
--
Richard Huxton
Archonet Ltd

No, not really

perhaps you can do a ORDER BY (oid || yourtimefield). So you have the
RecordOrder in the way the records where inserted.

Daniel

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?

Regards,

BTJ

Well, I don't.. But normal timespan is about 6-7 hours +- (so one can
assume max timespan = 12 hours really...)

BTJ

On Mon, 2003-09-29 at 13:40, Richard Huxton wrote:

Yes, I could do that... I was just hoping to catch the odd times when
records aren't inserted in order....

BTJ

On Mon, 2003-09-29 at 13:44, Daniel Schuchardt wrote:

On Monday 29 September 2003 12:47, Bjørn T Johansen wrote:

Well, I don't.. But normal timespan is about 6-7 hours +- (so one can
assume max timespan = 12 hours really...)

Well, if you don't know what order you want, how can you tell PG to show them
in that order?

I think you might want to log a full timestamp by the sound of it. I'm not
sure your information is well defined.
--
Richard Huxton
Archonet Ltd

Well I know the order I want!

The order should be like this during night time:

2230
2350
0100
0350

and

1030
1145
1230
1315

on day time...
But that was my initial question, "As far as I can tell, there is no way
to solve this without also supplying a date or am I missing something?"

BTJ

On Mon, 2003-09-29 at 15:22, Richard Huxton wrote:

=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no> writes:

But that was my initial question, "As far as I can tell, there is no way
to solve this without also supplying a date or am I missing something?"

You could possibly do it without, using some logic like this:
1. compute MAX(time) - MIN(time)
2. if less than 12 hours, assume no midnight wraparound, sort by
straight time.
3. if more than 12 hours, assume a wraparound, sort accordingly.

But it seems a heck of a lot easier and less error-prone to store
a full timestamp instead. What's your motivation for storing only
time, anyway? Not space savings --- the time and timestamp types
are both 8 bytes in PG.

regards, tom lane

On Monday 29 September 2003 20:19, Bjørn T Johansen wrote:

Well I know the order I want!

The order should be like this during night time:

2230
2350
0100
0350

and

1030
1145
1230
1315

on day time...
But that was my initial question, "As far as I can tell, there is no way
to solve this without also supplying a date or am I missing something?"

And when does night-time end and day-time begin? If you have times:
0500 0600 0900 1200 1500 1900 2200 2300
is that the order or do you want
2200 2300 0500 0600 0900 1200 1500 1900

If you can tell me that, then the problem can be solved by the code I provided
earlier

--
Richard Huxton
Archonet Ltd

Well, I won't get times with a span like that....

On Mon, 2003-09-29 at 21:53, Richard Huxton wrote:

Yes, it would be a lot easier... But I can't do that, because the time
fields are default values; i.e. the time is the same every week but not
the date...

BTJ

On Mon, 2003-09-29 at 21:38, Tom Lane wrote:

On Monday 29 September 2003 21:31, Bjørn T Johansen wrote:

Well, I won't get times with a span like that....

OK - if you can explain what the rules are, I can tell you how to sort them.

From the information you've given it's impossible. I can't see any way that

you can decide that the right order is:
2200 2300 0200 0400
and not
0200 0400 2200 2300

I'm guessing there's some more information available you haven't mentioned
yet.

--
Richard Huxton
Archonet Ltd

Why can't you just take my word for it, this is the way it should be
sorted....

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!

BTJ

On Tuesday 30 September 2003 09:00, Bjørn T Johansen wrote:

Why can't you just take my word for it, this is the way it should be
sorted....

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.

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!

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

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?

BTJ

Why can't you just take my word for it, this is the way it should be
sorted....

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

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

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

BTJ