On Jan 9, 2008 11:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Brendan Jurd" <direvus@gmail.com> writes:
> > On Jan 10, 2008 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> The spec's approach to datetime operations in general is almost totally
> >> brain-dead, ...
>
> > It's true that the spec fails to consider DST, in that it doesn't
> > partition "day" and "second" intervals separately.
>
> That's only one of the ways in which they ignore DST, and not even the
> most important one --- my vote for the spectacularly bad omission is
> that SET TIME ZONE only allows constant offsets from UTC.
I am assuming that you are advocating the use of the names for
timezones that can indicate what happens over a DST change. I think
that it would be useful to be able specify a timezone like PST8PDT.
> > Whether the spec is braindead w.r.t intervals or not, Postgres is
> > clearly giving the wrong answer.
>
> Sure, but it's not clear that there *is* a right answer. As noted
> upthread, a useful approximate answer can be better than no answer
> at all.
I am not sure that I agree with that. If you need to keep track of the
days, you should probably be using intervals using day to second (or
narrower) resolution.
> > None of these comparisons are sane.
>
> You can always refrain from making such comparisons, if you think they
> are incapable of yielding useful answers.
Maybe a way to enable strict compliance to the standard would be useful.
> This whole area is pretty messy, and I don't think that there is or can
> be a simple uniform solution :-(. We need to tread carefully in
> introducing new behaviors that we might regret later. So I'm not in
> favor of inventing an interval division operator that just duplicates
> functionality that's already there in a more-cumbersome notation.
> We might want that operator back someday. Who even wants to argue that
> the result datatype should be numeric? Dividing a three-component
> quantity by another one doesn't sound to me like an operation that
> naturally yields a scalar result.
I think this is reasonable.
wt