Re: [COMMITTERS] pgsql-server/ oc/src/sgml/datatype.sgml rc/bac ... - Mailing list pgsql-patches
| From | Bruce Momjian |
|---|---|
| Subject | Re: [COMMITTERS] pgsql-server/ oc/src/sgml/datatype.sgml rc/bac ... |
| Date | |
| Msg-id | 200312210437.hBL4b6E21367@candle.pha.pa.us Whole thread Raw |
| Responses |
Re: [COMMITTERS] pgsql-server/ oc/src/sgml/datatype.sgml
|
| List | pgsql-patches |
Tom Lane wrote: > momjian@svr1.postgresql.org (Bruce Momjian) writes: > > Attached is a patch that addressed all the discussed issues that > > did not break backward compatability, > > Was this patch actually agreed to? I hadn't gotten around to looking > at it because I thought Peter was still objecting to the whole idea. > > > including the ability to > > output ISO-8601 compliant intervals by setting datestyle to > > iso8601basic. > > This is a horrid, horrid idea. Datestyle is already a complete mess > because it is being used to control several things; it should have been > two or possibly three GUC variables not one. Sticking in yet another > behavior is just not acceptable IMHO, especially when it's defined as > non-orthogonally as that. > > Please revert that part of the patch and instead invent a new GUC > variable that's specifically for interval formatting. > > BTW, I can tell without looking that the patch is deficient in > documentation; if it has effects on GUC variables, why is there no > mod in runtime.sgml? OK, I have backed out the patch. Peter did object, but I said I thought the submitter had addressed the objections (backward compatibility) and I asked for someone to tell me the objections, and no one replied, so I assumed it was OK and it went through the standard patch application process. The patch is attached if someone wants to modify it to head in the direction that most developers prefer. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ? log ? config.log ? GNUmakefile ? config.status ? doc/src/sgml/HISTORY ? src/Makefile.custom ? src/Makefile.global ? src/log ? src/backend/postgres ? src/backend/catalog/postgres.bki ? src/backend/catalog/postgres.description ? src/backend/utils/mb/conversion_procs/conversion_create.sql ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0 ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0 ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0 ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0 ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0 ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0 ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0 ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0.0 ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0.0 ? src/bin/initdb/initdb ? src/bin/initlocation/initlocation ? src/bin/ipcclean/ipcclean ? src/bin/pg_config/pg_config ? src/bin/pg_controldata/pg_controldata ? src/bin/pg_ctl/pg_ctl ? src/bin/pg_dump/pg_dump ? src/bin/pg_dump/pg_restore ? src/bin/pg_dump/pg_dumpall ? src/bin/pg_encoding/pg_encoding ? src/bin/pg_resetxlog/pg_resetxlog ? src/bin/pgtclsh/pgtclsh ? src/bin/pgtclsh/pgtksh ? src/bin/psql/psql ? src/bin/scripts/createdb ? src/bin/scripts/dropdb ? src/bin/scripts/createlang ? src/bin/scripts/createuser ? src/bin/scripts/droplang ? src/bin/scripts/dropuser ? src/bin/scripts/clusterdb ? src/bin/scripts/vacuumdb ? src/include/pg_config.h ? src/include/stamp-h ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1.1 ? src/interfaces/ecpg/ecpglib/libecpg.so.4.1 ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1.1 ? src/interfaces/ecpg/preproc/ecpg ? src/interfaces/libpgtcl/libpgtcl.so.2.5 ? src/interfaces/libpq/libpq.so.3.2 ? src/pl/plperl/SPI.c ? src/pl/plperl/libplperl.so.0.0 ? src/pl/plpgsql/src/libplpgsql.so.1.0 ? src/pl/tcl/libpltcl.so.2.0 ? src/pl/tcl/modules/pltcl_loadmod ? src/pl/tcl/modules/pltcl_delmod ? src/pl/tcl/modules/pltcl_listmod ? src/test/regress/pg_regress ? src/test/regress/log ? src/test/regress/tmp_check ? src/test/regress/results ? src/test/regress/expected/copy.out ? src/test/regress/expected/create_function_1.out ? src/test/regress/expected/create_function_2.out ? src/test/regress/expected/misc.out ? src/test/regress/expected/constraints.out ? src/test/regress/sql/copy.sql ? src/test/regress/sql/create_function_1.sql ? src/test/regress/sql/create_function_2.sql ? src/test/regress/sql/misc.sql ? src/test/regress/sql/constraints.sql ? src/tools/thread/thread_test Index: doc/src/sgml/datatype.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v retrieving revision 1.135 retrieving revision 1.136 diff -c -r1.135 -r1.136 *** doc/src/sgml/datatype.sgml 1 Dec 2003 22:07:55 -0000 1.135 --- doc/src/sgml/datatype.sgml 20 Dec 2003 15:32:54 -0000 1.136 *************** *** 1,5 **** <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/datatype.sgml,v 1.135 2003/12/01 22:07:55 momjian Exp $ --> <chapter id="datatype"> --- 1,5 ---- <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/datatype.sgml,v 1.136 2003/12/20 15:32:54 momjian Exp $ --> <chapter id="datatype"> *************** *** 1785,1790 **** --- 1785,1841 ---- <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> *************** *** 1941,1946 **** --- 1992,2002 ---- <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> *************** *** 1995,2000 **** --- 2051,2061 ---- <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: /cvsroot/pgsql-server/src/backend/commands/variable.c,v retrieving revision 1.90 retrieving revision 1.91 diff -c -r1.90 -r1.91 *** src/backend/commands/variable.c 29 Nov 2003 19:51:48 -0000 1.90 --- src/backend/commands/variable.c 20 Dec 2003 15:32:54 -0000 1.91 *************** *** 9,15 **** * * * IDENTIFICATION ! * $PostgreSQL: pgsql-server/src/backend/commands/variable.c,v 1.90 2003/11/29 19:51:48 pgsql Exp $ * *------------------------------------------------------------------------- */ --- 9,15 ---- * * * IDENTIFICATION ! * $PostgreSQL: pgsql-server/src/backend/commands/variable.c,v 1.91 2003/12/20 15:32:54 momjian Exp $ * *------------------------------------------------------------------------- */ *************** *** 82,88 **** /* Ugh. Somebody ought to write a table driven version -- mjl */ ! if (strcasecmp(tok, "ISO") == 0) { newDateStyle = USE_ISO_DATES; scnt++; --- 82,93 ---- /* Ugh. Somebody ought to write a table driven version -- mjl */ ! if (strcasecmp(tok, "ISO8601BASIC") == 0) ! { ! newDateStyle = USE_ISO8601BASIC_DATES; ! scnt++; ! } ! else if (strcasecmp(tok, "ISO") == 0) { newDateStyle = USE_ISO_DATES; scnt++; *************** *** 197,202 **** --- 202,210 ---- { 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: /cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v retrieving revision 1.121 retrieving revision 1.122 diff -c -r1.121 -r1.122 *** src/backend/utils/adt/datetime.c 17 Dec 2003 21:45:44 -0000 1.121 --- src/backend/utils/adt/datetime.c 20 Dec 2003 15:32:54 -0000 1.122 *************** *** 8,14 **** * * * IDENTIFICATION ! * $PostgreSQL: pgsql-server/src/backend/utils/adt/datetime.c,v 1.121 2003/12/17 21:45:44 tgl Exp $ * *------------------------------------------------------------------------- */ --- 8,14 ---- * * * IDENTIFICATION ! * $PostgreSQL: pgsql-server/src/backend/utils/adt/datetime.c,v 1.122 2003/12/20 15:32:54 momjian Exp $ * *------------------------------------------------------------------------- */ *************** *** 37,42 **** --- 37,43 ---- 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] = { *************** *** 2888,2893 **** --- 2889,3134 ---- } + /* + * 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. *************** *** 2897,2903 **** --- 3138,3148 ---- * * 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) { *************** *** 2922,2927 **** --- 3167,3189 ---- 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--) { *************** *** 2999,3004 **** --- 3261,3267 ---- if (type == IGNORE_DTF) type = DTK_SECOND; + /* should this allow ',' for locales that use it ? */ if (*cp == '.') { fval = strtod(cp, &cp); *************** *** 3370,3375 **** --- 3633,3648 ---- -(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) *************** *** 3525,3530 **** --- 3798,3848 ---- } 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 */ *************** *** 3688,3693 **** --- 4006,4020 ---- } /* 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. * *************** *** 3695,3700 **** --- 4022,4035 ---- * 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) *************** *** 3777,3782 **** --- 4112,4159 ---- } 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, "@ "); *************** *** 3901,3907 **** } /* identically zero? then put in a unitless zero... */ ! if (!is_nonzero) { strcat(cp, "0"); cp += strlen(cp); --- 4278,4284 ---- } /* identically zero? then put in a unitless zero... */ ! if (!is_nonzero && (style!=USE_ISO8601BASIC_DATES)) { strcat(cp, "0"); cp += strlen(cp); Index: src/include/miscadmin.h =================================================================== RCS file: /cvsroot/pgsql-server/src/include/miscadmin.h,v retrieving revision 1.138 retrieving revision 1.139 diff -c -r1.138 -r1.139 *** src/include/miscadmin.h 29 Nov 2003 22:40:53 -0000 1.138 --- src/include/miscadmin.h 20 Dec 2003 15:32:55 -0000 1.139 *************** *** 12,18 **** * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * ! * $PostgreSQL: pgsql-server/src/include/miscadmin.h,v 1.138 2003/11/29 22:40:53 pgsql Exp $ * * NOTES * some of the information in this file should be moved to --- 12,18 ---- * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * ! * $PostgreSQL: pgsql-server/src/include/miscadmin.h,v 1.139 2003/12/20 15:32:55 momjian Exp $ * * NOTES * some of the information in this file should be moved to *************** *** 150,155 **** --- 150,157 ---- * 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 *************** *** 169,174 **** --- 171,177 ---- #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: /cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/dt.h,v retrieving revision 1.14 retrieving revision 1.15 diff -c -r1.14 -r1.15 *** src/interfaces/ecpg/pgtypeslib/dt.h 5 Oct 2003 11:12:00 -0000 1.14 --- src/interfaces/ecpg/pgtypeslib/dt.h 20 Dec 2003 15:32:55 -0000 1.15 *************** *** 21,26 **** --- 21,27 ---- #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: /cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/dt_common.c,v retrieving revision 1.12 retrieving revision 1.13 diff -c -r1.12 -r1.13 *** src/interfaces/ecpg/pgtypeslib/dt_common.c 5 Oct 2003 11:12:00 -0000 1.12 --- src/interfaces/ecpg/pgtypeslib/dt_common.c 20 Dec 2003 15:32:55 -0000 1.13 *************** *** 704,709 **** --- 704,719 ---- -(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) *************** *** 802,807 **** --- 812,862 ---- } 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: /cvsroot/pgsql-server/src/interfaces/ecpg/pgtypeslib/interval.c,v retrieving revision 1.7 retrieving revision 1.8 diff -c -r1.7 -r1.8 *** src/interfaces/ecpg/pgtypeslib/interval.c 3 Oct 2003 10:07:28 -0000 1.7 --- src/interfaces/ecpg/pgtypeslib/interval.c 20 Dec 2003 15:32:55 -0000 1.8 *************** *** 442,447 **** --- 442,458 ---- 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. * *************** *** 449,454 **** --- 460,473 ---- * 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) *************** *** 465,471 **** */ switch (style) { ! /* compatible with ISO date formats */ case USE_ISO_DATES: if (tm->tm_year != 0) { --- 484,495 ---- */ switch (style) { ! /* 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) { *************** *** 530,535 **** --- 554,601 ---- 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;
pgsql-patches by date: