Time problem again?

[Bjørn T Johansen]

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

[Richard Huxton]

Re: Time problem again?

On Monday 29 September 2003 10:11, Bjørn T Johansen wrote:[color=blue]
> 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?[/color]

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'::interva l
ELSE my_time - '6 hours'::interva l
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bjørn T Johansen]

Re: Time problem again?

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:
[color=blue]
> On Monday 29 September 2003 10:11, Bjørn T Johansen wrote:[color=green]
> > 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?[/color]
>
> 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'::interva l
> ELSE my_time - '6 hours'::interva l
> 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).[/color]

[Richard Huxton]

Re: Time problem again?

On Monday 29 September 2003 12:26, Bjørn T Johansen wrote:[color=blue]
> 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...[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Daniel Schuchardt]

Re: Time problem again?

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

[Bjørn T Johansen]

Re: Time problem again?

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:
[color=blue]
> On Monday 29 September 2003 12:26, Bjørn T Johansen wrote:[color=green]
> > 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...[/color]
>
> And how do you know that the first example shouldn't have been
> 0110
> 0330
> 2350[/color]

[Bjørn T Johansen]

Re: Time problem again?

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:
[color=blue]
> 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[/color]

[Richard Huxton]

Re: Time problem again?

On Monday 29 September 2003 12:47, Bjørn T Johansen wrote:[color=blue]
> Well, I don't.. But normal timespan is about 6-7 hours +- (so one can
> assume max timespan = 12 hours really...)[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Bjørn T Johansen]

Re: Time problem again?

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:
[color=blue]
> On Monday 29 September 2003 12:47, Bjørn T Johansen wrote:[color=green]
> > Well, I don't.. But normal timespan is about 6-7 hours +- (so one can
> > assume max timespan = 12 hours really...)[/color]
>
> 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.[/color]

[Tom Lane]

Re: Time problem again?

=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no > writes:[color=blue]
> 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?"[/color]

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

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

[Richard Huxton]

Re: Time problem again?

On Monday 29 September 2003 20:19, Bjørn T Johansen wrote:[color=blue]
> 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?"[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bjørn T Johansen]

Re: Time problem again?

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

On Mon, 2003-09-29 at 21:53, Richard Huxton wrote:
[color=blue]
> On Monday 29 September 2003 20:19, Bjørn T Johansen wrote:[color=green]
> > 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?"[/color]
>
> 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[/color]

[Bjørn T Johansen]

Re: Time problem again?

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:
[color=blue]
> =?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no > writes:[color=green]
> > 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?"[/color]
>
> 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[/color]

[Richard Huxton]

Re: Time problem again?

On Monday 29 September 2003 21:31, Bjørn T Johansen wrote:[color=blue]
> Well, I won't get times with a span like that....[/color]

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

---------------------------(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?

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:[color=blue]
> On Monday 29 September 2003 21:31, Bjørn T Johansen wrote:[color=green]
> > Well, I won't get times with a span like that....[/color]
>
> 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.[/color]


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