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 126.96.36.199 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] ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster