Thread: Time problem again?

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

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

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

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:

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

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:

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

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:

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.

=?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:

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

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:

Bjørn 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 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 Tue, 2003-09-30 at 09:40, Richard Huxton 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.

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

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.