Tom Lane wrote:
>> If I read SQL 200N's spec correctly
>> select interval '-1 1:00:00';
>> should mean "-1 days -1 hours",
>> yet 8.3 sees it as "-1 days +1 hours".
>
> I think we are kind of stuck on this one. If we change it, then how
> would one represent -1 days +1 hours? The spec's format is only sane
I'm not proposing this, but I could imagine making
"-1 -1:00:00" and/or "-1 +1:00:00" mean -1 days +1 hours.
I think if it weren't for backward compatibility issues I'd even
support such an idea - since now we're oh-so-very-close to accepting
to-spec literals. Unfortunately I fear old users might assume the
opposite meaning.
>> Scary to touch that one, but since a standard's a standard, I think
>> eventually we should get there.
>
> The SQL spec's date/time handling is, and always has been, broken enough
> that I feel no great compulsion to follow every last detail. Especially
> details that make it impossible to support our extensions...
In this case we're so very close to meeting the spec, though. And
it's ashame where we accept the to-spec syntax ("-1 1:00:00") but
return a different answer.
And since it's not quite impossible to support our extensions (force
putting the sign on the h:m:s part for mixed sign cases) I feels nice
to me to try.