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

Bruce Momjian writes:

> Is this ready for application? It looks good to me. However, there is
> an "Open issues" section.

It would be more useful to implement the SQL standard for intervals first
instead of inventing more nonstandard formats for it.

>
> ---------------------------------------------------------------------------
>
> 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
> >
>
>

--
Peter Eisentraut peter_e(at)gmx(dot)net