Re: Proposed patch: make SQL interval-literal syntax work per spec

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> Oh. I wasn't proposing 8601-only. Just the one-character
> shorthands like '1Y1M'::interval that postgresql interprets
> as "1 year one minute". No standard specifies anything close
> to that; and any similar standards ask to interpret that M as
> months instead of minutes.

Hmmm. I would say that the problem with that is not that it's
nonstandard but that it's ambiguous. Our documentation about the
interval type says:

interval values can be written with the following syntax:

[(at)] quantity unit [quantity unit...] [direction]

Where: quantity is a number (possibly signed); unit is microsecond,
millisecond, second, minute, hour, day, week, month, year, decade,
century, millennium, or abbreviations or plurals of these units;
direction can be ago or empty. The at sign (@) is optional noise. The
amounts of different units are implicitly added up with appropriate
sign accounting. ago negates all the fields.

There isn't anything there that would suggest to a user that 'm' is
well-defined as a unit, much less that it specifically means "minute"
rather than one of the other options. What if we just tweak the code to
reject ambiguous abbreviations?

[ experiments a bit... ] Another interesting point is that "mo",
which is a perfectly unique abbreviation, is rejected. Seems like
the handling of abbreviations in this code could be improved.

regards, tom lane