Re: [HACKERS] bug or change in functionality in 7.2?

On Fri, 16 Nov 2001 00:26:40 EST, Tom Lane wrote:
> Barry Lind <barry(at)xythos(dot)com> writes:
> > select period_start + interval('1 hour') from periods;
> > This worked in 7.1, but in 7.2 I am getting the following error:
> > ERROR: parser: parse error at or near "'"
>
> "interval" is a more reserved word than it used to be ("timestamp"
> is too). This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps. That means using "interval"
> as an unquoted function name doesn't work anymore.
>
> I concur with Christopher's recommendation: use the syntax
> interval '1 hour'
> Other possibilities are
> cast('1 hour' as interval)
> "interval"('1 hour')
> '1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.

In my readings on the standard, the first one is _not_ SQL92
standard notation. Indeed, I may be incorrect since I do not have an
actual copy of the SQL92 standard. I am basing my statements on Date/
Darwin's "A guide to the SQL Standard", fourth edition. In that tome,
they state:

----- cut -----
day-time:

Written as the key word INTERVAL, followed by a (day-time) interval
string consisting of an opening single quote, an optional sign, a
continuous nonempty subsequence of dd, hh, mm, and ss[.[nnnnnn]] (with
a space separator between dd and the rest, if dd is specified, and
colon separators elsewhere), and a closing single quote, followed by
the appropriate "start [TO end]" specification.

Examples:

INTERVAL '1' MINUTE
INTERVAL '2 12' DAY TO HOUR
INTERVAL '2:12:35' HOUR TO SECOND
INTERVAL '-4.50' SECOND

----- cut -----

In my experiences with other databases, the notations indicated in
the Date/Darwin book do indeed work whereas the PostgreSQL notation
(with the closing single quote following the start to end
specification) do not work.

Thanks,
F Harvell

--
Mr. F Harvell Phone: +1.407.673.2529
FTS International Data Systems, Inc. Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302 Fax: +1.407.673.4472
Winter Park, FL 32792 mailto:fharvell(at)fts(dot)net