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]