Is this ready for application? It looks good to me. However, there is an "Open issues" section.
--------------------------------------------------------------------------- Ron Mayer wrote: > 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. > > > Index: doc/src/sgml/datatype.sgml > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v > retrieving revision 1.123 > diff -u -1 -0 -r1.123 datatype.sgml > --- doc/src/sgml/datatype.sgml 31 Aug 2003 17:32:18 -0000 1.123 > +++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 -0000 > @@ -1735,20 +1735,71 @@ > Quantities of days, hours, minutes, and seconds can be specified without > explicit unit markings. For example, <literal>'1 12:59:10'</> is read > the same as <literal>'1 day 12 hours 59 min 10 sec'</>. > </para> > > <para> > The optional precision > <replaceable>p</replaceable> should be between 0 and 6, and > defaults to the precision of the input literal. > </para> > + > + > + <para> > + Alternatively, <type>interval</type> values can be written as > + ISO 8601 time intervals, using the "Format with time-unit designators". > + This format always starts with the character <literal>'P'</>, followed > + by a string of values followed by single character time-unit designators. > + A <literal>'T'</> separates the date and time parts of the interval. > + </para> > + > + <para> > + Format: PnYnMnDTnHnMnS > + </para> > + <para> > + In this format, <literal>'n'</> gets replaced by a number, and > + <literal>Y</> represents years, > + <literal>M</> (in the date part) months, > + <literal>D</> months, > + <literal>H</> hours, > + <literal>M</> (in the time part) minutes, > + and <literal>S</> seconds. > + </para> > + > + > + <table id="interval-example-table"> > + <title>Interval Example</title> > + <tgroup cols="2"> > + <thead> > + <row> > + <entry>Traditional</entry> > + <entry>ISO-8601 time-interval</entry> > + </row> > + </thead> > + <tbody> > + <row> > + <entry>1 month</entry> > + <entry>P1M</entry> > + </row> > + <row> > + <entry>1 hour 30 minutes</entry> > + <entry>PT1H30M</entry> > + </row> > + <row> > + <entry>2 years 10 months 15 days 10 hours 30 minutes 20 > seconds</entry> > + <entry>P2Y10M15DT10H30M20S</entry> > + </row> > + </tbody> > + </thead> > + </table> > + > + </para> > </sect3> > > <sect3> > <title>Special Values</title> > > <indexterm> > <primary>time</primary> > <secondary>constants</secondary> > </indexterm> > > Index: src/backend/utils/adt/datetime.c > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v > retrieving revision 1.116 > diff -u -1 -0 -r1.116 datetime.c > --- src/backend/utils/adt/datetime.c 27 Aug 2003 23:29:28 -0000 1.116 > +++ src/backend/utils/adt/datetime.c 8 Sep 2003 04:04:59 -0000 > @@ -30,20 +30,21 @@ > struct tm * tm, fsec_t *fsec, int *is2digits); > static int DecodeNumberField(int len, char *str, > int fmask, int *tmask, > struct tm * tm, fsec_t *fsec, int *is2digits); > static int DecodeTime(char *str, int fmask, int *tmask, > struct tm * tm, fsec_t *fsec); > static int DecodeTimezone(char *str, int *tzp); > static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel); > static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm); > static void TrimTrailingZeros(char *str); > +static int DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, > struct tm * tm, fsec_t *fsec); > > > int day_tab[2][13] = { > {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}, > {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}}; > > char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", > "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL}; > > char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday", > @@ -2872,30 +2873,271 @@ > default: > *val = tp->value; > break; > } > } > > return type; > } > > > +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale); > +{ > + int sec; > + fval *= scale; > + sec = fval; > + tm->tm_sec += sec; > +#ifdef HAVE_INT64_TIMESTAMP > + *fsec += ((fval - sec) * 1000000); > +#else > + *fsec += (fval - sec); > +#endif > +} > + > + > +/* DecodeISO8601Interval() > + * > + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of > + * time-interval by duration only." > + * Basic extended format: PnYnMnDTnHnMnS > + * PnW > + * For more info. > + * http://www.astroclark.freeserve.co.uk/iso8601/index.html > + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF > + * > + * Examples: P1D for 1 day > + * PT1H for 1 hour > + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min > + * > + * The first field is exactly "p" or "pt" it may be of this type. > + * > + * Returns -1 if the field is not of this type. > + * > + * It pretty strictly checks the spec, with the two exceptions > + * that a week field ('W') may coexist with other units, and that > + * this function allows decimals in fields other than the least > + * significant units. > + */ > +int > +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, > fsec_t *fsec) > +{ > + char *cp; > + int fmask = 0, > + tmask; > + int val; > + double fval; > + int arg; > + int datepart; > + > + /* > + * An ISO 8601 "time-interval by duration only" must start > + * with a 'P'. If it contains a date-part, 'p' will be the > + * only character in the field. If it contains no date part > + * it will contain exactly to characters 'PT' indicating a > + * time part. > + * Anything else is illegal and will be treated like a > + * traditional postgresql interval. > + */ > + if (!(field[0][0] == 'p' && > + ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0)))) > + { > + return -1; > + } > + > + > + /* > + * If the first field is exactly 1 character ('P'), it starts > + * with date elements. Otherwise it's two characters ('PT'); > + * indicating it starts with a time part. > + */ > + datepart = (field[0][1] == 0); > + > + /* > + * Every value must have a unit, so we require an even > + * number of value/unit pairs. Therefore we require an > + * odd nubmer of fields, including the prefix 'P'. > + */ > + if ((nf & 1) == 0) > + return -1; > + > + /* > + * Process pairs of fields at a time. > + */ > + for (arg = 1 ; arg < nf ; arg+=2) > + { > + char * value = field[arg ]; > + char * units = field[arg+1]; > + > + /* > + * The value part must be a number. > + */ > + if (ftype[arg] != DTK_NUMBER) > + return -1; > + > + /* > + * extract the number, almost exactly like the non-ISO interval. > + */ > + val = strtol(value, &cp, 10); > + > + /* > + * One difference from the normal postgresql interval below... > + * ISO 8601 states that "Of these, the comma is the preferred > + * sign" so I allow it here for locales that support it. > + * Note: Perhaps the old-style interval code below should > + * allow for this too, but I didn't want to risk backward > + * compatability. > + */ > + if (*cp == '.' || *cp == ',') > + { > + fval = strtod(cp, &cp); > + if (*cp != '\0') > + return -1; > + > + if (val < 0) > + fval = -(fval); > + } > + else if (*cp == '\0') > + fval = 0; > + else > + return -1; > + > + > + if (datepart) > + { > + /* > + * All the 8601 unit specifiers are 1 character, but may > + * be followed by a 'T' character if transitioning between > + * the date part and the time part. If it's not either > + * one character or two characters with the second being 't' > + * it's an error. > + */ > + if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0))) > + return -1; > + > + if (units[1] == 't') > + datepart = 0; > + > + switch (units[0]) /* Y M D W */ > + { > + case 'd': > + tm->tm_mday += val; > + if (fval != 0) > + adjust_fval(fval,tm,fsec, 86400); > + tmask = ((fmask & DTK_M(DAY)) ? 0 : > DTK_M(DAY)); > + break; > + > + case 'w': > + tm->tm_mday += val * 7; > + if (fval != 0) > + adjust_fval(fval,tm,fsec,7 * 86400); > + tmask = ((fmask & DTK_M(DAY)) ? 0 : > DTK_M(DAY)); > + break; > + > + case 'm': > + tm->tm_mon += val; > + if (fval != 0) > + adjust_fval(fval,tm,fsec,30 * 86400); > + tmask = DTK_M(MONTH); > + break; > + > + case 'y': > + /* > + * Why can fractional months produce seconds, > + * but fractional years can't? Well the older > + * interval code below has the same property > + * so this one follows the other one too. > + */ > + tm->tm_year += val; > + if (fval != 0) > + tm->tm_mon += (fval * 12); > + tmask = ((fmask & DTK_M(YEAR)) ? 0 : > DTK_M(YEAR)); > + break; > + > + default: > + return -1; /* invald date unit prefix */ > + } > + } > + else > + { > + /* > + * ISO 8601 time part. > + * In the time part, only one-character > + * unit prefixes are allowed. If it's more > + * than one character, it's not a valid ISO 8601 > + * time interval by duration. > + */ > + if (units[1] != 0) > + return -1; > + > + switch (units[0]) /* H M S */ > + { > + case 's': > + tm->tm_sec += val; > +#ifdef HAVE_INT64_TIMESTAMP > + *fsec += (fval * 1000000); > +#else > + *fsec += fval; > +#endif > + tmask = DTK_M(SECOND); > + break; > + > + case 'm': > + tm->tm_min += val; > + if (fval != 0) > + adjust_fval(fval,tm,fsec,60); > + tmask = DTK_M(MINUTE); > + break; > + > + case 'h': > + tm->tm_hour += val; > + if (fval != 0) > + adjust_fval(fval,tm,fsec,3600); > + tmask = DTK_M(HOUR); > + break; > + > + default: > + return -1; /* invald time unit prefix */ > + } > + } > + fmask |= tmask; > + } > + > + if (*fsec != 0) > + { > + int sec; > + > +#ifdef HAVE_INT64_TIMESTAMP > + sec = (*fsec / INT64CONST(1000000)); > + *fsec -= (sec * INT64CONST(1000000)); > +#else > + TMODULO(*fsec, sec, 1e0); > +#endif > + tm->tm_sec += sec; > + } > + return (fmask != 0) ? 0 : -1; > +} > + > + > /* DecodeInterval() > * Interpret previously parsed fields for general time interval. > * Returns 0 if successful, DTERR code if bogus input detected. > * > * Allow "date" field DTK_DATE since this could be just > * an unsigned floating point number. - thomas 1997-11-16 > * > * Allow ISO-style time span, with implicit units on number of days > * preceding an hh:mm:ss field. - thomas 1998-04-30 > + * > + * Allow ISO-8601 style "Representation of time-interval by duration only" > + * of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30 > */ > + > int > DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t > *fsec) > { > int is_before = FALSE; > char *cp; > int fmask = 0, > tmask, > type; > int i; > int dterr; > @@ -2906,20 +3148,37 @@ > > type = IGNORE_DTF; > tm->tm_year = 0; > tm->tm_mon = 0; > tm->tm_mday = 0; > tm->tm_hour = 0; > tm->tm_min = 0; > tm->tm_sec = 0; > *fsec = 0; > > + /* > + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of > + * time-interval by duration only." > + * Basic extended format: PnYnMnDTnHnMnS > + * PnW > + * http://www.astroclark.freeserve.co.uk/iso8601/index.html > + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF > + * Examples: P1D for 1 day > + * PT1H for 1 hour > + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min > + * > + * The first field is exactly "p" or "pt" it may be of this type. > + */ > + if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) { > + return 0; > + } > + > /* read through list backwards to pick up units before values */ > for (i = nf - 1; i >= 0; i--) > { > switch (ftype[i]) > { > case DTK_TIME: > dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec); > if (dterr) > return dterr; > type = DTK_DAY; > @@ -2983,20 +3242,21 @@ > } > /* DROP THROUGH */ > > case DTK_DATE: > case DTK_NUMBER: > val = strtol(field[i], &cp, 10); > > if (type == IGNORE_DTF) > type = DTK_SECOND; > > + /* should this allow ',' for locales that use it ? */ > if (*cp == '.') > { > fval = strtod(cp, &cp); > if (*cp != '\0') > return DTERR_BAD_FORMAT; > > if (val < 0) > fval = -(fval); > } > else if (*cp == '\0') > > =================================================================== > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
