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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
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: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.orgjavascript:; ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
Jeff Davis pg...@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° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
Re: [GENERAL] Range-Types in 9.2
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-ow...@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
Re: [GENERAL] Range-Types in 9.2
Marc Mamin m.ma...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
