Thread

Re: interval( 'seconds 27960' ) is broken

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-08T07:15:48Z

> > In 7.0.3, I'm converting seconds from midnight to a time of
> > day using interval ( 'seconds <val>').  This worked fine in
> > 7.0.3 and gives a Bad interval external representation message
> > in 7.1.
> This has never been the intended or documented format; it should
> be '<val> seconds'.  Allowing both is ambiguous, so I'd say that
> the behavior change is a correct one.

In fact, the leading "seconds" label in the above example was never
actually used. What was happening was that the bare "<val>" was
interpreted as having units of seconds, and the leading label had no
effect.

It may help to notice that interval parsing proceeds from right to left,
mostly to allow the units specifier (e.g. "seconds") to be read before
the actual value. A leading units specifier is simply ignored, since
there is no value to the left for it to apply to.

In 7.1, a unitless number is not allowed for an interval, at least
partly because SQL9x requires time zones to be considered intervals
under some conditions. So the default handling of intervals had to be
consistant with that usage.

I would recommend specifying things as "interval ( '<val> seconds' )".

                      - Thomas