Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators"

Tom wrote...
> At this point it should move to pghackers, I think.

Background for pghackers first, open issues below...
  Over on pgpatches we've been discussing ISO syntax for  “time intervals” of the “format with time-unit designators”.
http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php  A short summary is that I’ve submitted a patch that
acceptsintervals of this format..    Postgresql interval:              ISO8601 Interval
---------------------------------------------------   '1 year 6 months'                'P1Y6M'    '3 hours 25 minutes
42seconds'  'PT3H25M42S'  The final draft is here    ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
 
  This patch was backward-compatable, but further improvements  discussed on patches may break compatability so I
wantedto  discuss them here before implementing them.   I’ll also  be submitting a new datestyle “iso8601” to output
theseintervals.
 

Open issues:

1. Postgresql supported a shorthand for intervals that had  a similar, but not compatable syntax:    Interval
ISO             Existing postgres                        8601            shorthand
-----------------------------------------------------   '1 year 1 minute'   'P1YT1M'         '1Y1M'    '1 year 1 month'
  'P1Y1M'          N/A
 
  The current thinking of the thread in pgpatches is to remove  the existing (undocumented) syntax.
  Removing this will break backward compatability if anyone  used this feature.  Let me know if you needed it.

2. Some of the parsing for intervals is inconsistant and  confusing.  For example, note that ‘0.01 years’ is  less than
‘0.01months’.
 
 betadb=# select '0.01 month'::interval as hundredth_of_month, betadb-#        '0.01 year'::interval  as
hundredth_of_year; hundredth_of_month | hundredth_of_year --------------------+-------------------  07:12:00
|00:00:00
 
  This occurs because the current interval parsing rounds  fractional years to the month, but fractional months  to the
fractionof a second.
 
  The current thinking on the thread in patches is  at the very least to make these consistant, but with  some
open-issuesbecause months aren’t a fixed number  of days, and days aren’t a fixed number of seconds.
 
  The easiest and most minimal change would be to assume  that any fractional part automatically gets turned  into
seconds,assuming things like 30 seconds/month,  24 hrs/day.  Since all units except years work that way  today, it’d
wouldhave the least impact on existing code.
 
  A probably better way that Tom recommended would remember  fractional months and fractional days.  This has the
advantagethat unlike today,    ‘.5 months’::interval + ‘.5 months’::interval  would then equal 1 month.
 
  So what should ‘.5 years’ be?
  Today, it’s ‘6 mons’.  But I could just as easily  argue that it should be 365.2425/2 days, or 4382.91  seconds.
Eachof these will be different (the last  two are different durring daylight savings).
 

3. This all is based on the final draft standard of  ISO 8601, but I haven’t seen the actual expensive  standard.  If
anyonehas it handy...
 
  Also, I’m curious to know what if anything the SQL  spec says about intervals and units.  Any pointers.
 Ron

Any other interval annoyances I should hit at the same time?