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