Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with - Mailing list pgsql-hackers

Here is an email on the open issues.

---------------------------------------------------------------------------

Ron Mayer wrote:
> 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
>    accepts intervals of this format..
>      Postgresql interval:              ISO8601 Interval
>      ---------------------------------------------------
>      '1 year 6 months'                'P1Y6M'
>      '3 hours 25 minutes 42 seconds'  '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 wanted to
>    discuss them here before implementing them.   I?ll also
>    be submitting a new datestyle ?iso8601? to output these intervals.
> 
> 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.01 months?.
> 
>   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 fraction of a second.
> 
>    The current thinking on the thread in patches is
>    at the very least to make these consistant, but with
>    some open-issues because 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 would have the least impact on existing code.
> 
>    A probably better way that Tom recommended would remember
>    fractional months and fractional days.  This has the
>    advantage that 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.  Each of 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 anyone has 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?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073