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

This has been saved for the 7.5 release:

http:/momjian.postgresql.org/cgi-bin/pgpatches2

Feel free to submit an updated patch that rips out the old syntax, as
discussed, or replace this patch with a more comprehensive one.

---------------------------------------------------------------------------

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073