Thread: Range-Types in 9.2

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

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