Re: operator suggest " interval / interval = numeric"

On Jan 9, 2008 10:44 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On Jan 10, 2008 5:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > The spec's approach to datetime operations in general is almost totally
> > brain-dead, and so you won't find a lot of support around here for hewing
> > to the straight-and-narrow-spec-compliance approach. If they have not
> > even heard of daylight-savings time, how can anyone credit them with any
> > meaningful contact with the real world? We'll cite the spec where it
> > suits us, but in this area "the spec says you can't do that" carries
> > very little weight.
>
> It's true that the spec fails to consider DST, in that it doesn't
> partition "day" and "second" intervals separately.

Should the standard really do that? I mean, a day really is defined as
some number of seconds.

> But is that really a reason to reject the concept of interval
> partitioning altogether? It seems the spec has the right idea, it
> just doesn't take it far enough to cover all the bases.

I think the standard does a good job with the partitioning.

> Whether the spec is braindead w.r.t intervals or not, Postgres is
> clearly giving the wrong answer. A year interval is not 360 day
> intervals long. A month interval is not shorter than 31 day
> intervals. And, thanks to the geniuses who came up with DST, a day
> interval is not the same as 24 hour intervals anymore. None of these
> comparisons are sane.

DST has no bearing on the fact that a day is still 86400 in the mean
solar system. There really is no partition for Day down through
seconds. It just means that for timestamp operations the day where we
spring forward is 23 hours long, and the day where we fall back is 1
day 1 hour.

Having said all this, neither a month nor a year is not a fixed number
of days. The partitioning system used by the SQL standard seems to
deal with this problem pretty well.

wt