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.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 



Tue, 28 Aug 2001 13:50:57 +0100
"Oliver Elphick" <olly@lfix.co.uk>
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.
...
> Valid SQL92 syntax that is not currently supported:
...
>   junk=# SELECT INTERVAL '1990' YEAR(4);
...

So far, I've had shift/reduce troubles trying to have a trailing
qualifier field like this.

>   junk=# SELECT INTERVAL -'1-1' YEAR TO MONTH;
>   ERROR:  parser: parse error at or near "YEAR"

A leading sign in front of a string-like field? Yuck.

...
> Valid interval value format not currently supported:
>   year-month

I'll look at accepting this for the current INTERVAL type too.

> 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).

I like this point. Really, SQL99 intervals are a bit unwieldy, though
they do have "extra features" which someone might find useful.

> The main difference would be in the output format...
> ... parts of either year-month or
> day hour:minute:second.fractional_second

We could probably support this format (now that you have described it to
us) at least for the "SQL" datestyle even for the existing INTERVAL
type.

> 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.

This set of conventions might let the date/time parser do a complete
job. I put in the "days" text label to reduce the ambiguity of a single,
unlabeled integer.

> What do you think?

Have you gotten started yet? Finished yet?? ;)

                        - Thomas

Thu, 30 Aug 2001 14:53:25 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: INTERVAL type: SQL92 implementation