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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073