Skip site navigation (1) Skip section navigation (2)

INTERVAL type: SQL92 implementation

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: INTERVAL type: SQL92 implementation
Date: 2001-08-28 12:50:57
Message-ID: 200108281250.f7SCovxM008421@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: 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>
                                   [,<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 



Responses

pgsql-hackers by date

Next:From: Alex PilosovDate: 2001-08-28 13:08:01
Subject: Re: Toast,bytea, Text -blob all confusing
Previous:From: Stephan SzaboDate: 2001-08-28 12:44:33
Subject: Re: performance question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group