INTERVAL type: SQL92 implementation

If full SQL92 implementation of INTERVAL would be a welcome addition,
could it be added as a TODO item? I would like to work on it, since I
want to use some features that are not currently supported.

SQL92 INTERVAL data type (also SQL99, I think):

<interval type> ::= INTERVAL {{<start field> TO <end field>} |
<single datetime field>}

<start field> ::= <non-second datetime field>
[(<interval leading field precision>)]

<end field> ::= <non second datetime field> |
SECOND [(<fractional seconds precision>)]

<single datetime field> ::= <non-second datetime field>
[(<interval leading field precision>)] |
SECOND[(<interval leading field precision>
[,<fractional seconds precision>])]

<non-second datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE

0 < <interval leading field precision> < implementation defined maximum
(default is 2)

0 <= <fractional seconds precision> < 10
(default is 6)

INTERVALs may be defined by a range within either YEAR TO MONTH or
DAY TO SECOND.

INTERVAL literals are defined as:

INTERVAL [+|-]'<value string>' <interval qualifier>

<interval qualifier> ::= <start field> [TO <end field>]

Part of this syntax is supported by the parser, but not consistently.

Valid SQL92 syntax that is not currently supported:

junk=# SELECT INTERVAL '1990' YEAR(4);
ERROR: parser: parse error at or near "("
junk=# select INTERVAL '1990' YEAR;
ERROR: Bad interval external representation '1990'
junk=# SELECT INTERVAL -'1-1' YEAR TO MONTH;
ERROR: parser: parse error at or near "YEAR"
junk=# SELECT INTERVAL +'100 0:0:0.1' DAY(3) TO SECOND;
ERROR: parser: parse error at or near "DAY"
junk=# SELECT INTERVAL +'100 0:0:0.1' DAY TO SECOND;
ERROR: parser: parse error at or near "DAY"
junk=# -- actually, it doesn't like the +
junk=# SELECT INTERVAL '0:0:0.0:' HOUR TO SECOND(9);
ERROR: parser: parse error at or near "("
junk=# SELECT INTERVAL '100000.001' SECOND(6,3);
ERROR: parser: parse error at or near "("
junk=# SELECT INTERVAL '100000.001' SECOND;
?column?
-------------------
1 day 03:46:40.00
(1 row)

junk=# -- should output '100000.001'
junk=# SELECT INTERVAL -'10' MINUTE;
ERROR: parser: parse error at or near "MINUTE"
junk=# SELECT INTERVAL '1:1' HOUR(6) TO MINUTE;
ERROR: parser: parse error at or near "("

Valid interval value format not currently supported:

year-month

Since there are aspects of SQL92 interval representation that clash
with the current implementation, I would suggest that current
practice be followed unless SQL92 syntax is used. So a field that
is of type INTERVAL without qualification would continue to work
as it does now (except that I would like to implement range checking).

The main difference would be in the output format. For a
SQL92-compliant interval column, the output would be the appropriate
parts of either

year-month

or

day hour:minute:second.fractional_second

according to the field definition, without any words (i.e.: "1 03:46:40.00"
instead of "1 day 03:46:40.00", and "3-5" instead of "3 years 5 mons").
All parts within the range will be shown, even if they are trailing zeros.

The other difference would be that input values would be range-checked
to see that they didn't exceed the possible range of the type; so
the range of INTERVAL HOUR(3) TO MINUTE would be 0 seconds to
+|-999:59:59.999999 and inserting a value outside the range would be
an error. Intervals of the current type also need range-checking:

junk=# select interval '199999999 years';
?column?
--------------------------
-157913942 years -4 mons
(1 row)

What do you think?

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Use hospitality one to another without grudging."
I Peter 4:9