Skip site navigation (1) Skip section navigation (2)

Re: ISO 8601 'Time Intervals' of the 'format with time-unit deignators'

From: <andrew(at)dunslane(dot)net>
To: <ron(at)intervideo(dot)com>
Cc: <pgsql-patches(at)postgresql(dot)org>
Subject: Re: ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
Date: 2003-09-08 06:32:17
Message-ID: 2096.24.211.141.25.1063002737.squirrel@www.dunslane.net (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-generalpgsql-hackerspgsql-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]



In response to

Responses

pgsql-hackers by date

Next:From: Mark KirkwoodDate: 2003-09-08 09:22:32
Subject: dump cache summary
Previous:From: Jeroen Ruigrok/asmodaiDate: 2003-09-08 05:52:39
Subject: Re: FreeBSD/i386 thread test

pgsql-patches by date

Next:From: Manfred KoizarDate: 2003-09-08 08:05:21
Subject: Re: Minor lmgr code cleanup
Previous:From: Tom LaneDate: 2003-09-08 05:47:17
Subject: Re: ISO 8601 "Time Intervals" of the "format with time-unit deignators"

pgsql-advocacy by date

Next:From: Anton de WetDate: 2003-09-08 09:43:52
Subject: Re: Need a good slogan to use for ...
Previous:From: Tom LaneDate: 2003-09-08 05:47:17
Subject: Re: ISO 8601 "Time Intervals" of the "format with time-unit deignators"

pgsql-general by date

Next:From: Nigel J. AndrewsDate: 2003-09-08 06:44:24
Subject: Re: tsearch CVS and differences to version for 7.3.x
Previous:From: Tom LaneDate: 2003-09-08 05:47:17
Subject: Re: ISO 8601 "Time Intervals" of the "format with time-unit deignators"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group