So far,
I have submitted the input-part.
I have a working output-part (attached below, but I'm
still cleaning up the documentation so I'll submit another
one later). The output is chosen by setting
the datestyle to 'iso8601basic'.
Those two changes don't break backward compatability
but don't fix too much odd behavior except ISO time interval I/O.
I was encouraged to look into changing the way timestamp
math is done (keeping month, and day, and second separate
until the end). This is a bigger change and I don't have
a stable version yet, and it breaks backward compatability.
I hope to submit a proposal for changes early enough in the
7.5 timeframe to submit fixes then as well. At the very least
I will fully document the existing interval-math as part of
this proposal so the docs can be updated even if the proposal
gets rejected.
Ron
> -----Original Message-----
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Friday, September 26, 2003 3:54 PM
> To: Bruce Momjian
> Cc: Ron Mayer; Peter Eisentraut; [EMAIL PROTECTED];
> [EMAIL PROTECTED]
> Subject: Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with
> time-unit deignators'
>
>
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Where did we leave this?
>
> I thought it was proposed work for 7.5.
>
> regards, tom lane
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- saved from url=(0027)http://192.168.50.90/latest -->
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR></HEAD>
<BODY><PRE>Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.124
diff -u -r1.124 datatype.sgml
--- doc/src/sgml/datatype.sgml 12 Sep 2003 22:17:22 -0000 1.124
+++ doc/src/sgml/datatype.sgml 26 Sep 2003 22:57:58 -0000
@@ -1742,6 +1742,57 @@
<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>
@@ -1897,6 +1948,11 @@
<entry>regional style</entry>
<entry>17.12.1997 07:37:16.00 PST</entry>
</row>
+ <row>
+ <entry>ISO8601basic</entry>
+ <entry>ISO 8601 basic format</entry>
+ <entry>19971217T073716-08</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -1951,6 +2007,11 @@
<programlisting>
<optional> <replaceable>quantity</>
<replaceable>unit</> <optional> ... </> </>
<optional> <replaceable>days</> </> <optional>
<replaceable>hours</>:<replaceable>minutes</>:<replaceable>sekunden</>
</optional>
</programlisting>
+ </para>
+
+ <para>
+ If the <varname>datestyle</> is set to iso8601basic, the interval
+ output is a ISO-8601 time interval with time-unit designator (like P1Y6M or
PT23H59M59S).
</para>
<para>
Index: src/backend/commands/variable.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/variable.c,v
retrieving revision 1.87
diff -u -r1.87 variable.c
--- src/backend/commands/variable.c 4 Aug 2003 02:39:58 -0000 1.87
+++ src/backend/commands/variable.c 26 Sep 2003 22:57:58 -0000
@@ -82,7 +82,12 @@
/* Ugh. Somebody ought to write a table driven version -- mjl */
- if (strcasecmp(tok, "ISO") == 0)
+ if (strcasecmp(tok, "ISO8601BASIC") == 0)
+ {
+ newDateStyle = USE_ISO8601BASIC_DATES;
+ scnt++;
+ }
+ else if (strcasecmp(tok, "ISO") == 0)
{
newDateStyle = USE_ISO_DATES;
scnt++;
@@ -197,6 +202,9 @@
{
case USE_ISO_DATES:
strcpy(result, "ISO");
+ break;
+ case USE_ISO8601BASIC_DATES:
+ strcpy(result, "ISO8601BASIC");
break;
case USE_SQL_DATES:
strcpy(result, "SQL");
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 -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 26 Sep 2003 22:57:59 -0000
@@ -37,6 +37,7 @@
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] = {
@@ -2879,6 +2880,246 @@
}
+/*
+ * A small helper function to avoid cut&paste code in DecodeIso8601Interval
+ */
+static 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.
@@ -2888,7 +3129,11 @@
*
* 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)
{
@@ -2913,6 +3158,23 @@
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--)
{
@@ -2990,6 +3252,7 @@
if (type == IGNORE_DTF)
type = DTK_SECOND;
+ /* should this allow ',' for locales that use it ? */
if (*cp == '.')
{
fval = strtod(cp, &cp);
@@ -3362,6 +3625,16 @@
-(tm->tm_year - 1), tm->tm_mon,
tm->tm_mday, "BC");
break;
+ case USE_ISO8601BASIC_DATES:
+ /* compatible with ISO date formats */
+ if (tm->tm_year > 0)
+ sprintf(str, "%04d%02d%02d",
+ tm->tm_year, tm->tm_mon,
tm->tm_mday);
+ else
+ sprintf(str, "%04d%02d%02d %s",
+ -(tm->tm_year - 1), tm->tm_mon,
tm->tm_mday, "BC");
+ break;
+
case USE_SQL_DATES:
/* compatible with Oracle/Ingres date formats */
if (DateOrder == DATEORDER_DMY)
@@ -3517,6 +3790,51 @@
}
break;
+ case USE_ISO8601BASIC_DATES: // BASIC
+ /* Compatible with ISO-8601 date formats */
+
+ sprintf(str, "%04d%02d%02dT%02d%02d",
+ ((tm->tm_year > 0) ? tm->tm_year :
-(tm->tm_year - 1)),
+ tm->tm_mon, tm->tm_mday, tm->tm_hour,
tm->tm_min);
+
+ /*
+ * Print fractional seconds if any. The field widths here
+ * should be at least equal to MAX_TIMESTAMP_PRECISION.
+ *
+ * In float mode, don't print fractional seconds before 1 AD,
+ * since it's unlikely there's any precision left ...
+ */
+#ifdef HAVE_INT64_TIMESTAMP
+ if (fsec != 0)
+ {
+ sprintf((str + strlen(str)), "%02d.%06d",
tm->tm_sec, fsec);
+#else
+ if ((fsec != 0) && (tm->tm_year > 0))
+ {
+ sprintf((str + strlen(str)), "%09.6f", tm->tm_sec +
fsec);
+#endif
+ TrimTrailingZeros(str);
+ }
+ else
+ sprintf((str + strlen(str)), "%02d", tm->tm_sec);
+
+ if (tm->tm_year <= 0)
+ sprintf((str + strlen(str)), " BC");
+
+ /*
+ * tzp == NULL indicates that we don't want *any* time zone
+ * info in the output string. *tzn != NULL indicates that we
+ * have alpha time zone info available. tm_isdst != -1
+ * indicates that we have a valid time zone translation.
+ */
+ if ((tzp != NULL) && (tm->tm_isdst >= 0))
+ {
+ hour = -(*tzp / 3600);
+ min = ((abs(*tzp) / 60) % 60);
+ sprintf((str + strlen(str)), ((min != 0) ?
"%+03d:%02d" : "%+03d"), hour, min);
+ }
+ break;
+
case USE_SQL_DATES:
/* Compatible with Oracle/Ingres date formats */
@@ -3680,6 +3998,15 @@
} /* EncodeDateTime() */
+/*
+ * Small helper function to avoid cut&paste in EncodeInterval below
+ */
+static char * AppendISO8601Fragment(char * cp, int value, char character)
+{
+ sprintf(cp,"%d%c",value,character);
+ return cp + strlen(cp);
+}
+
/* EncodeInterval()
* Interpret time structure as a delta time and convert to string.
*
@@ -3687,6 +4014,14 @@
* Actually, afaik ISO does not address time interval formatting,
* but this looks similar to the spec for absolute date/time.
* - thomas 1998-04-30
+ *
+ * Actually, afaik, ISO 8601 does specify formats for "time
+ * intervals...[of the]...format with time-unit designators", which
+ * are pretty ugly. The format looks something like
+ * P1Y1M1DT1H1M1.12345S
+ * If you want this (perhaps for interoperability with computers
+ * rather than humans), datestyle 'iso8601basic' will output these.
+ * - ron 2003-07-14
*/
int
EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str)
@@ -3769,6 +4104,48 @@
}
break;
+ case USE_ISO8601BASIC_DATES:
+ sprintf(cp,"P");
+ cp++;
+ if (tm->tm_year != 0) cp =
AppendISO8601Fragment(cp,tm->tm_year,'Y');
+ if (tm->tm_mon != 0) cp =
AppendISO8601Fragment(cp,tm->tm_mon ,'M');
+ if (tm->tm_mday != 0) cp =
AppendISO8601Fragment(cp,tm->tm_mday,'D');
+ if ((tm->tm_hour != 0) || (tm->tm_min != 0) ||
+ (tm->tm_sec != 0) || (fsec != 0))
+ {
+ sprintf(cp,"T"),
+ cp++;
+ }
+ if (tm->tm_hour != 0) cp =
AppendISO8601Fragment(cp,tm->tm_hour,'H');
+ if (tm->tm_min != 0) cp =
AppendISO8601Fragment(cp,tm->tm_min ,'M');
+
+ if ((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))
+ {
+ sprintf(cp,"T0S"),
+ cp+=2;
+ }
+ else if (fsec != 0)
+ {
+#ifdef HAVE_INT64_TIMESTAMP
+ sprintf(cp, "%d", abs(tm->tm_sec));
+ cp += strlen(cp);
+ sprintf(cp, ".%6dS", ((fsec >= 0) ? fsec :
-(fsec)));
+#else
+ fsec += tm->tm_sec;
+ sprintf(cp, "%fS", fabs(fsec));
+#endif
+ TrimTrailingZeros(cp);
+ cp += strlen(cp);
+ }
+ else if (tm->tm_sec != 0)
+ {
+ cp = AppendISO8601Fragment(cp,tm->tm_sec ,'S');
+ cp += strlen(cp);
+ }
+ break;
+
case USE_POSTGRES_DATES:
default:
strcpy(cp, "@ ");
@@ -3893,7 +4270,7 @@
}
/* identically zero? then put in a unitless zero... */
- if (!is_nonzero)
+ if (!is_nonzero && (style!=USE_ISO8601BASIC_DATES))
{
strcat(cp, "0");
cp += strlen(cp);
Index: src/include/miscadmin.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/miscadmin.h,v
retrieving revision 1.133
diff -u -r1.133 miscadmin.h
--- src/include/miscadmin.h 26 Aug 2003 15:38:25 -0000 1.133
+++ src/include/miscadmin.h 26 Sep 2003 22:57:59 -0000
@@ -143,6 +143,8 @@
* USE_ISO_DATES specifies ISO-compliant format
* USE_SQL_DATES specifies Oracle/Ingres-compliant format
* USE_GERMAN_DATES specifies German-style dd.mm/yyyy
+ * USE_ISO8601BASIC_DATES specifies ISO-8601-basic format (including
+ * ISO compliant but non-human-friendly intervals)
*
* DateOrder defines the field order to be assumed when reading an
* ambiguous date (anything not in YYYY-MM-DD format, with a four-digit
@@ -162,6 +164,7 @@
#define USE_ISO_DATES 1
#define USE_SQL_DATES 2
#define USE_GERMAN_DATES 3
+#define USE_ISO8601BASIC_DATES 4
/* valid DateOrder values */
#define DATEORDER_YMD 0
Index: src/interfaces/ecpg/pgtypeslib/dt.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/dt.h,v
retrieving revision 1.13
diff -u -r1.13 dt.h
--- src/interfaces/ecpg/pgtypeslib/dt.h 9 Sep 2003 10:46:38 -0000 1.13
+++ src/interfaces/ecpg/pgtypeslib/dt.h 26 Sep 2003 22:57:59 -0000
@@ -21,6 +21,7 @@
#define USE_ISO_DATES 1
#define USE_SQL_DATES 2
#define USE_GERMAN_DATES 3
+#define USE_ISO8601BASIC_DATES 4
#define DAGO "ago"
#define EPOCH "epoch"
Index: src/interfaces/ecpg/pgtypeslib/dt_common.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/dt_common.c,v
retrieving revision 1.11
diff -u -r1.11 dt_common.c
--- src/interfaces/ecpg/pgtypeslib/dt_common.c 9 Sep 2003 10:46:38 -0000 1.11
+++ src/interfaces/ecpg/pgtypeslib/dt_common.c 26 Sep 2003 22:58:00 -0000
@@ -715,6 +715,16 @@
-(tm->tm_year - 1), tm->tm_mon,
tm->tm_mday, "BC");
break;
+ case USE_ISO8601BASIC_DATES:
+ /* compatible with ISO date formats */
+ if (tm->tm_year > 0)
+ sprintf(str, "%04d%02d%02d",
+ tm->tm_year, tm->tm_mon,
tm->tm_mday);
+ else
+ sprintf(str, "%04d%02d%02d %s",
+ -(tm->tm_year - 1), tm->tm_mon,
tm->tm_mday, "BC");
+ break;
+
case USE_SQL_DATES:
/* compatible with Oracle/Ingres date formats */
if (EuroDates)
@@ -813,6 +823,51 @@
}
else
sprintf((str + strlen(str)), ":%02d", tm->tm_sec);
+
+ if (tm->tm_year <= 0)
+ sprintf((str + strlen(str)), " BC");
+
+ /*
+ * tzp == NULL indicates that we don't want *any* time zone
+ * info in the output string. *tzn != NULL indicates that we
+ * have alpha time zone info available. tm_isdst != -1
+ * indicates that we have a valid time zone translation.
+ */
+ if ((tzp != NULL) && (tm->tm_isdst >= 0))
+ {
+ hour = -(*tzp / 3600);
+ min = ((abs(*tzp) / 60) % 60);
+ sprintf((str + strlen(str)), ((min != 0) ?
"%+03d:%02d" : "%+03d"), hour, min);
+ }
+ break;
+
+ case USE_ISO8601BASIC_DATES:
+ /* Compatible with ISO-8601 date formats */
+
+ sprintf(str, "%04d%02d%02dT%02d%02d",
+ ((tm->tm_year > 0) ? tm->tm_year :
-(tm->tm_year - 1)),
+ tm->tm_mon, tm->tm_mday, tm->tm_hour,
tm->tm_min);
+
+ /*
+ * Print fractional seconds if any. The field widths here
+ * should be at least equal to MAX_TIMESTAMP_PRECISION.
+ *
+ * In float mode, don't print fractional seconds before 1 AD,
+ * since it's unlikely there's any precision left ...
+ */
+#ifdef HAVE_INT64_TIMESTAMP
+ if (fsec != 0)
+ {
+ sprintf((str + strlen(str)), "%02d.%06d",
tm->tm_sec, fsec);
+#else
+ if ((fsec != 0) && (tm->tm_year > 0))
+ {
+ sprintf((str + strlen(str)), "%09.6f", tm->tm_sec +
fsec);
+#endif
+ TrimTrailingZeros(str);
+ }
+ else
+ sprintf((str + strlen(str)), "%02d", tm->tm_sec);
if (tm->tm_year <= 0)
sprintf((str + strlen(str)), " BC");
Index: src/interfaces/ecpg/pgtypeslib/interval.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/interval.c,v
retrieving revision 1.6
diff -u -r1.6 interval.c
--- src/interfaces/ecpg/pgtypeslib/interval.c 9 Sep 2003 10:46:38 -0000 1.6
+++ src/interfaces/ecpg/pgtypeslib/interval.c 26 Sep 2003 22:58:00 -0000
@@ -443,6 +443,17 @@
return (fmask != 0) ? 0 : -1;
} /* DecodeInterval() */
+
+/*
+ * Small helper function to avoid cut&paste in EncodeInterval below
+ */
+static char * AppendISO8601Fragment(char * cp, int value, char character)
+{
+ sprintf(cp,"%d%c",value,character);
+ return cp + strlen(cp);
+}
+
+
/* EncodeInterval()
* Interpret time structure as a delta time and convert to string.
*
@@ -450,6 +461,14 @@
* Actually, afaik ISO does not address time interval formatting,
* but this looks similar to the spec for absolute date/time.
* - thomas 1998-04-30
+ *
+ * Actually, afaik, ISO 8601 does specify formats for "time
+ * intervals...[of the]...format with time-unit designators", which
+ * are pretty ugly. The format looks something like
+ * P1Y1M1DT1H1M1.12345S
+ * If you want this (perhaps for interoperability with computers
+ * rather than humans), datestyle 'iso8601basic' will output these.
+ * - ron 2003-07-14
*/
int
EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str)
@@ -466,7 +485,12 @@
*/
switch (style)
{
- /* compatible with ISO date formats */
+ /* compatible with ISO date formats
+ ([ram] Not for ISO 8601, perhaps some other ISO format.
+ but I'm leaving it that way because it's more human
+ readable than ISO8601 time intervals and for backwards
+ compatability.)
+ */
case USE_ISO_DATES:
if (tm->tm_year != 0)
{
@@ -531,6 +555,48 @@
cp += strlen(cp);
is_nonzero = TRUE;
}
+ }
+ break;
+
+ case USE_ISO8601BASIC_DATES:
+ sprintf(cp,"P");
+ cp++;
+ if (tm->tm_year != 0) cp =
AppendISO8601Fragment(cp,tm->tm_year,'Y');
+ if (tm->tm_mon != 0) cp =
AppendISO8601Fragment(cp,tm->tm_mon ,'M');
+ if (tm->tm_mday != 0) cp =
AppendISO8601Fragment(cp,tm->tm_mday,'D');
+ if ((tm->tm_hour != 0) || (tm->tm_min != 0) ||
+ (tm->tm_sec != 0) || (fsec != 0))
+ {
+ sprintf(cp,"T"),
+ cp++;
+ }
+ if (tm->tm_hour != 0) cp =
AppendISO8601Fragment(cp,tm->tm_hour,'H');
+ if (tm->tm_min != 0) cp =
AppendISO8601Fragment(cp,tm->tm_min ,'M');
+
+ if ((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))
+ {
+ sprintf(cp,"T0S"),
+ cp+=2;
+ }
+ else if (fsec != 0)
+ {
+#ifdef HAVE_INT64_TIMESTAMP
+ sprintf(cp, "%d", abs(tm->tm_sec));
+ cp += strlen(cp);
+ sprintf(cp, ".%6dS", ((fsec >= 0) ? fsec :
-(fsec)));
+#else
+ fsec += tm->tm_sec;
+ sprintf(cp, "%fS", fabs(fsec));
+#endif
+ TrimTrailingZeros(cp);
+ cp += strlen(cp);
+ }
+ else if (tm->tm_sec != 0)
+ {
+ cp = AppendISO8601Fragment(cp,tm->tm_sec ,'S');
+ cp += strlen(cp);
}
break;
</PRE></BODY></HTML>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
