Re: Proposed patch: make SQL interval-literal syntax work per spec - Mailing list pgsql-hackers

>>> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> The patch includes regression test changes that illustrate what it
does.
> I am not sure about some of the corner cases --- anyone want to see
if
> their understanding of the spec for <interval string> is different?
The patch seems to support extensions to the standard.
(1)  In the spec, an interval value or literal must be either
year-month or day-time.  (I guess they didn't want to try to deal with
the sticky issues of what it means to have an interval of, for
example, seven months and three days -- since an interval has no sense
of which seven months.)
(2)  The interval qualifier is not optional in the spec.
(3)  It seems to me that they were requiring that there be a
one-to-one match between the fields specified in the interval
qualifier and the fields present in the interval string.
(4)  I'm not 100% sure on this one, but it seemed to me that they were
requiring year to be four digits and other components (except for
fractional seconds) to be two digits.
So long as they are documented, there's nothing wrong with extensions.
Nothing I saw suggests that legal interval literals would be
misinterpreted.
> There is still some unfinished business if anyone wants to make it
> really exactly 100% spec compliant.  In particular the spec seems to
> allow a minus sign *outside* the string literal,
I agree.  They go so far as to point out how it should be interpreted
if the minus is present in both allowed locations (both inside and
outside the quotes):
5) The <sign> in a <signed numeric literal> or an <interval literal> is
a monadic arithmetic operator. The  monadic arithmetic operators + and * specify monadic plus and
monadic minus, respectively. If neither  monadic plus nor monadic minus are specified in a <signed numeric
literal> or an <interval literal>, or if  monadic plus is specified, then the literal is positive. If monadic
minus is specified in a <signed numeric  literal> or <interval literal>, then the literal is negative. If
<sign> is specified in both possible locations in  an <interval literal>, then the sign of the literal is determined
by
normal mathematical interpretation of  multiple sign operators.
> and if I'm reading it
> right, a precision spec in combination with field restrictions ought
to
> look like INTERVAL '...' DAY TO SECOND(3) not INTERVAL(3) '...' DAY
TO
> SECOND.
Agreed.
> However, for these you'll get a syntax error instead of
> silently wrong answers if you try to use the other syntax, so it's
not
> quite as pernicious as the matters addressed here.
Agreed.
-Kevin