Range-Types in 9.2

On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote:

Hi all,
great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?

CREATE TYPE timerange AS RANGE ( subtype = time );

That's the simple answer. I believe we discussed including this as a
built-in range type at some point, but decided against it. I can't
remember the reason right now.

Regards,
Jeff Davis

hello,

agree about this great feature :)

There is another point I've wondered about:

Is there some logical reason why no function width(range) was added to the bundle ?
not a big deal, but width(range) looks just nicer than upper(range)-lower(range)

best regards,

Marc Mamin

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Jeff Davis
Sent: Fri 8/3/2012 7:42 PM
To: Andreas Kretschmer
Cc: pg-general
Subject: Re: [GENERAL] Range-Types in 9.2

On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote:

Hi all,
great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?

CREATE TYPE timerange AS RANGE ( subtype = time );

That's the simple answer. I believe we discussed including this as a
built-in range type at some point, but decided against it. I can't
remember the reason right now.

Regards,
Jeff Davis

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Marc Mamin" <M.Mamin@intershop.de> writes:

Is there some logical reason why no function width(range) was added to the bundle ?

It's not well-defined for all base types. A range type only presumes
the underlying type has comparison, not that it has subtraction.
Moreover, there's no way to define range(anyrange) polymorphically,
because the types that do have subtraction don't necessarily have
operators that return the same type. (timestamptz being the first
counterexample.)

regards, tom lane

Jeff Davis <pgsql@j-davis.com> wrote:

On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote:

Hi all,
great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?

CREATE TYPE timerange AS RANGE ( subtype = time );

Thx.

That's the simple answer. I believe we discussed including this as a
built-in range type at some point, but decided against it. I can't
remember the reason right now.

Okay, but is it possible to write down this as an example in the
documentation? I think there are a LOT of possible use-cases for
TIMERANGE ... (or, better, include it as build-in ...)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 08/03/2012 08:06 AM, Andreas Kretschmer wrote:

Hi all,
great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?

Regards, Andreas

Time ranges could be more complicated than you realize. You'd have
problems if you wanted to create a range that extends beyond midnight.
Say for example, you need a range from 10 PM to 4 AM. We realize it as
being 4AM the following day, But if you tried to enter it into a time
range, it would throw an exception for having an upper bound smaller
than the lower bounds.

You might have to use something like int4range to represent minutes or
seconds of the day and add some helper functions. So for minutes you'd
represent 10 PM to 4 AM as [1320, 1680)

On Fri, 2012-08-03 at 10:42 -0700, Jeff Davis wrote:

On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote:

great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?

CREATE TYPE timerange AS RANGE ( subtype = time );

That's the simple answer. I believe we discussed including this as a
built-in range type at some point, but decided against it. I can't
remember the reason right now.

Time of day is a cycle (I forget who pointed this out), so a limit of
24:00:00 is fairly restrictive. It happens that daytime ranges like
[14:00,15:00) are more common; but it doesn't seem unreasonable to say
[22:00,02:00) either.

So, an interpretation where time of day has a total order is only useful
really for a daytime schedule (which is still useful, but perhaps not
general enough to include in core). We might be able to make it work as
ranges within a 24-hour cycle, but that will require more thought.

Regards,
Jeff Davis

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Jeff Davis
Sent: Monday, August 06, 2012 1:52 PM
To: Andreas Kretschmer
Cc: pg-general
Subject: Re: [GENERAL] Range-Types in 9.2

On Fri, 2012-08-03 at 10:42 -0700, Jeff Davis wrote:

On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote:

great feature, but i can't find a TIMERANGE, i want to store
time-ranges, for instance [10:00:00,16:00:00), how can i do that?

CREATE TYPE timerange AS RANGE ( subtype = time );

That's the simple answer. I believe we discussed including this as a
built-in range type at some point, but decided against it. I can't
remember the reason right now.

Time of day is a cycle (I forget who pointed this out), so a limit of
24:00:00 is fairly restrictive. It happens that daytime ranges like
[14:00,15:00) are more common; but it doesn't seem unreasonable to say
[22:00,02:00) either.

So, an interpretation where time of day has a total order is only useful really
for a daytime schedule (which is still useful, but perhaps not general enough
to include in core). We might be able to make it work as ranges within a 24-
hour cycle, but that will require more thought.

Regards,
Jeff Davis

Because hours are based upon a zero-cycle it is possible to define a time range as thus:

Start: 15:00
End: 03:00
Maximum: 24:00
Minimum: 00:00 { More generalized, can reset to any value }
Iterations: 2

Basically a "cyclical range"...

The question becomes in what situations would this be more useful than an explicit starting and ending timestamp (with date).

It would likely just be easier to create a custom "time" variant that allows for values greater than "24:00" and less-than "00:00" and then create a range from that.

Maybe name it "relativetime"...

David J.

Hi,

I just wonder about scenario in which time range would be usefull? (I mean,
just time - not timestamp...)

We have some scenario where we use time range as settings... Concrete case
is: for each hour employee worked between 20:00 and 08:00 should be paid x,
between 08:00 - 20:00 y... ( stored in table as timestamp range with check
constraint for lower to fixed date, just because of must not overlap
constraint - though could be and 2 time columns...)

Now for actuall working period (timestamp range), we are building dynamic
timestamp ranges, taking date from actual working period and time from
settings, to calculate hours what belongs to x, y rates...

Thanks,

Misa

On Monday, August 6, 2012, Jeff Davis wrote: