Re: Range types

On Wed, 2009-12-16 at 12:42 -0500, Robert Haas wrote:
> On Wed, Dec 16, 2009 at 12:31 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > There's one problem, and that's for timestamptz ranges with intervals
> > that include days and months. Timezone adjustments are just not
> > well-defined for that kind of granule (nor would it be particularly
> > useful even if it magically worked), so this would have to be blocked
> > somehow. I think that's a special case, and we could provide the user
> > with a nice error message telling the user to use a date or timestamp
> > range instead.
>
> This seems like a fairly special-purpose type. You'd be targeting it
> at people who are very concerned with storing large numbers of these
> (so they really care about space consumption) but for some reason
> don't need to mix days and months (actually, the current interval
> representation stores days, months, and seconds separately). I
> certainly think this might be useful to some people but it doesn't
> really sounds like a general range type facility, since it seems to
> involve some hacks that are fairly datatype-specific.

My statement should have read "days or months". In other words, you
can't have a timestamptz range with a granularity of '3 days'. But if
that's your granularity, as Scott says, you should be using a date
range, not a timestamptz range.

Timestamptz ranges are only really useful when you have a granularity
measured in seconds (or some fraction or multiple thereof). Otherwise,
the timezone adjustment doesn't make any sense.

So this isn't a case of limited functionality, just that we need to
inform the user that a timestamptz range with granularity '1 day' or '1
month' makes no sense.

Regards,
Jeff Davis