Re: ISO 8601 'Time Intervals' of the 'format with time-unit deignators' - Mailing list pgsql-patches

Is there a way of producing as well as reading this format? Or did I miss
something?

cheers

andrew

Ron Mayer said:
> Short summary:
>
>   This patch allows ISO 8601 "time intervals" using the "format
>   with time-unit designators" to specify postgresql "intervals".
>
>   Below I have (A) What these time intervals are, (B) What I
>   modified to support them, (C) Issues with intervals I want
>   to bring up, and (D) a patch supporting them.
>
>   It's helpful to me.  Any feedback is appreciated.  If you
>   did want to consider including it, let me know what to clean
>   up.  If not, I thought I'd just put it here if anyone else finds it
>   useful too.
>
>   Thanks for your time,
>
>      Ron Mayer
>
> Longer:
>
> (A) What these intervals are.
>
>   ISO 8601, the standard from which PostgreSQL gets some of it's  time
>   syntax, also has a specification for "time-intervals".
>
>   In particular, section 5.5.4.2 has a "Representation of
>   time-interval by duration only" which I believe maps
>   nicely to ISO intervals.
>
>   Compared to the ISO 8601 time interval specification, the
>   postgresql interval syntax is quite verbose.  For example:
>
>     Postgresql interval:              ISO8601 Interval
>     ---------------------------------------------------
>     '1 year 6 months'                'P1Y6M'
>     '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
>
>   Yeah, it's uglier, but it sure is short which can make
>   for quicker typing and shorter scripts, and if for some
>   strange reason you had an application using this format
>   it's nice not to have to translate.
>
>   The syntax is as follows:
>       Basic extended format:  PnYnMnDTnHnMnS
>                               PnW
>
>       Where everything before the "T" is a date-part and everything
>       after is a time-part.  W is for weeks.
>       In the date-part, Y=Year, M=Month,  D=Day
>       In the time-part, H=Hour, M=Minute, S=Second
>
>   Much more info can be found from the draft standard
>   ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
>   The final standard's only available for $$$ so I didn't
>   look at it.  Some other sites imply that this part didn't
>   change from the last draft to the standard.
>
>
> (B) This change was made by adding two functions to "datetime.c"
>    next to where DecodeInterval parses the normal interval syntax.
>
>    A total of 313 lines were added, including comments and sgml docs.
>    Of these only 136 are actual code, the rest, comments, whitespace,
>    etc.
>
>
>    One new function "DecodeISO8601Interval" follows the style of
>    "DecodeInterval" below it, and trys to strictly follow the ISO
>    syntax.  If it doesn't match, it'll return -1 and the old syntax
>    will be checked as before.
>
>    The first test (first character of the first field must be 'P',  and
>    second character must be 'T' or '\0') should be fast so I don't
>    think this will impact performance of existing code.
>
>
>    The second function ("adjust_fval") is just a small helper-function
>    to remove some of the cut&paste style that DecodeInterval used.
>
>    It seems to work.
>
=======================================================================
>    betadb=# select 'P1M15DT12H30M7S'::interval;
>            interval
>    ------------------------
>     1 mon 15 days 12:30:07
>    (1 row)
>
>    betadb=# select '1 month 15 days 12 hours 30 minutes 7
>    seconds'::interval;
>         interval
>    ------------------------
>    1 mon 15 days 12:30:07
>    (1 row)
>    =====================================================================
>
>
>
> (C) Open issues with intervals, and questions I'd like to ask.
>
>    1.  DecodeInterval seems to have a hardcoded '.' for specifying
>        fractional times.  ISO 8601 states that both '.' and ',' are ok,
>        but "of these, the comma is the preferred sign".
>
>        In DecodeISO8601Interval I loosened the test to allow
>        both but left it as it was in DecodeInterval.  Should
>        both be changed to make them more consistant?
>
>    2.  In "DecodeInterval", fractional weeks and fractional months
>        can produce seconds; but fractional years can not (rounded to
>        months).  I didn't understand the reasoning for this, so I left
>        it the same, and followed the same convention for
>        ISO intervals.  Should I change this?
>
>    3.  I could save a bunch of copy-paste-lines-of-code from the
>        pre-existing DecodeInterval by calling the adjust_fval helper
>        function.  The tradeoff is a few extra function-calls when
>        decoding an interval.  However I didn't want to risk changes to
>        the existing part unless you guys encourage me to do so.
>
>
> (D) The patch.
>
>
[snip]