INTERVAL type: SQL92 implementation - Mailing list pgsql-hackers

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>
[,<fractionalseconds 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
errorat 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.Elphick@lfix.co.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