*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 1975,1980 **** January 8 04:05:06 1999 PST
--- 1975,1996 ----
+ Alternatively, interval values can be written as
+ ISO 8601 time intervals, using the "Format with time-unit designators",
+ or PnYnMnDTnHnMnS. This format always starts with the character
+ 'P', followed by a string of values followed by single
+ character time-unit designators. A 'T' separates the
+ date and time parts of the interval.
+ In this format, 'n' gets replaced by a number, and
+ Y represents years,
+ M (in the date part) months,
+ D months,
+ H hours,
+ M (in the time part) minutes,
+ and S seconds.
+
+
+
Internally interval values are stored as months, days,
and seconds. This is done because the number of days in a month
varies, and a day can have 23 or 25 hours if a daylight savings
***************
*** 2224,2231 **** January 8 04:05:06 1999 PST
The output format of the interval types can be set to one of the four
! styles sql_standard,
! postgres, or postgres_verbose.The default
is the postgres format.
shows examples of each
--- 2240,2247 ----
The output format of the interval types can be set to one of the four
! styles sql_standard, iso_8601,
! postgres, or postgres_verbose. The default
is the postgres format.
shows examples of each
***************
*** 2244,2249 **** January 8 04:05:06 1999 PST
--- 2260,2281 ----
+ The iso_8601 style will output ISO 8601
+ time intervals using the "format with time-unit designators"
+ This format always starts with the character
+ 'P', followed by a string of values followed by single
+ character time-unit designators. A 'T' separates the
+ date and time parts of the interval.
+ In this format, 'n' gets replaced by a number, and
+ Y represents years,
+ M (in the date part) months,
+ D months,
+ H hours,
+ M (in the time part) minutes,
+ and S seconds.
+
+
+
The postgres style will output intervals that match
the style PostgreSQL 8.3 outputed when the
parameter was set to ISO.
***************
*** 2274,2283 **** January 8 04:05:06 1999 PST
-1-2 +3 -4:05:06
postgres
1 year 2 mons
3 days 04:05:06
! -1 years -2 mons +3 days -04:05:06
postgres_verbose
--- 2306,2321 ----
-1-2 +3 -4:05:06
+ iso_8601
+ P1Y2M
+ P3DT4H5M6
+ P-1Y-2M3DT-4H-5M-6
+
+
postgres
1 year 2 mons
3 days 04:05:06
! -1 year -2 mons 3 days -04:05:06
postgres_verbose
***************
*** 2293,2299 **** January 8 04:05:06 1999 PST
Note that sql_standard style will only produce strictly
standards-conforming string sliterals when given a strictly SQL-standard interval
value - meaning that it needs to be a pure year-month or datetime
! interval and not mix positive and negative components.
--- 2331,2339 ----
Note that sql_standard style will only produce strictly
standards-conforming string sliterals when given a strictly SQL-standard interval
value - meaning that it needs to be a pure year-month or datetime
! interval and not mix positive and negative components, and
! iso_8601 style will only product strictly ISO-8601 standard
! strings when given values that can be represented as ISO-8601 intervals.
*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***************
*** 248,253 **** assign_intervalstyle(const char *value, bool doit, GucSource source)
--- 248,257 ----
{
newIntervalStyle = INTSTYLE_SQL_STANDARD;
}
+ else if (pg_strcasecmp(value, "iso_8601") == 0)
+ {
+ newIntervalStyle = INTSTYLE_ISO_8601;
+ }
else
{
ereport(GUC_complaint_elevel(source),
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 2723,2728 **** DecodeSpecial(int field, char *lowtoken, int *val)
--- 2723,2906 ----
}
+ /*
+ * A small helper function to avoid cut&paste code in DecodeIso8601Interval
+ */
+ static void adjust_fval(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
+ {
+ int sec;
+ if (fval == 0) return;
+ 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 DTERR_BAD_FORMAT 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 *str, struct pg_tm * tm, fsec_t *fsec)
+ {
+ char unit;
+ int fmask = 0,
+ tmask;
+ int val;
+ double fval;
+ int datepart = true;
+ int negative = false;
+
+ 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;
+
+ /*
+ * 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 does not match an ISO 8601 basic interval
+ * and will be treated like a traditional postgresql interval.
+ */
+ if (!(str[0] == 'P'))
+ {
+ return DTERR_BAD_FORMAT;
+ }
+ str++;
+
+ /*
+ * Process pairs of fields at a time.
+ */
+ while (*str)
+ {
+ if (*str == 'T')
+ {
+ datepart = false;
+ str++;
+ continue;
+ }
+ if (! (isdigit(*str) || *str == '-' || *str=='.') )
+ return DTERR_BAD_FORMAT;
+
+ negative = (*str == '-');
+ fval = strtod(str, &str);
+ val = fval;
+ fval = fval - val;
+
+ unit = *str;
+ str++;
+
+ if (datepart)
+ {
+ switch (unit) /* Y M D W */
+ {
+ case 'D':
+ tm->tm_mday += val;
+ adjust_fval(fval,tm,fsec, 86400);
+ tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+ break;
+
+ case 'W':
+ tm->tm_mday += val * 7;
+ adjust_fval(fval,tm,fsec,7 * 86400);
+ tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+ break;
+
+ case 'M':
+ tm->tm_mon += val;
+ 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 DTERR_BAD_FORMAT; /* not a vald ISO8601 date unit prefix */
+ }
+ }
+ else
+ {
+ switch (unit) /* H M S */
+ {
+ case 'S':
+ tm->tm_sec += val;
+ adjust_fval(fval,tm,fsec,1);
+ tmask = DTK_M(SECOND);
+ break;
+ case 'M':
+ tm->tm_min += val;
+ adjust_fval(fval,tm,fsec,60);
+ tmask = DTK_M(MINUTE);
+ break;
+ case 'H':
+ tm->tm_hour += val;
+ adjust_fval(fval,tm,fsec,3600);
+ tmask = DTK_M(HOUR);
+ break;
+ default:
+ return DTERR_BAD_FORMAT; /* not a vald ISO8601 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.
***************
*** 2732,2737 **** DecodeSpecial(int field, char *lowtoken, int *val)
--- 2910,2919 ----
*
* 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 mayer 2003-08-30
+ * also deprecate the not-really-iso-like style from 1998-04-30
*/
int
DecodeInterval(char **field, int *ftype, int nf, int range,
***************
*** 3605,3613 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
return TRUE;
}
! /*
! * small helper funciton to avoid copy&paste of this ifdef below
*/
static void
AppendFsec(char * cp,fsec_t fsec)
{
--- 3787,3803 ----
return TRUE;
}
! /*
! * Small helper functions to avoid cut&paste in EncodeInterval below
*/
+
+ static char *
+ AddISO8601IntervalPart(char * cp, int value, char units)
+ {
+ if (value==0) return cp;
+ return cp + sprintf(cp,"%d%c",value,units);
+ }
+
static void
AppendFsec(char * cp,fsec_t fsec)
{
***************
*** 3647,3652 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3837,3846 ----
bool is_nonzero = FALSE;
char *cp = str;
+ /*
+ * These are used so much below, copy them to local variables to make
+ * the code shorter and more readable.
+ */
int year = tm->tm_year;
int mon = tm->tm_mon;
int mday = tm->tm_mday;
***************
*** 3730,3735 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3924,3957 ----
}
break;
}
+ case INTSTYLE_ISO_8601:
+ if ((year == 0) && (mon == 0) && (mday == 0) &&
+ (hour == 0) && (min == 0) && (sec == 0) &&
+ (fsec == 0))
+ {
+ sprintf(cp,"PT0S");
+ break;
+ }
+ cp += sprintf(cp,"P");
+ cp = AddISO8601IntervalPart(cp,year,'Y');
+ cp = AddISO8601IntervalPart(cp,mon ,'M');
+ cp = AddISO8601IntervalPart(cp,mday,'D');
+ if ((hour != 0) || (min != 0) ||
+ (sec != 0) || (fsec != 0))
+ {
+ cp += sprintf(cp,"T");
+ }
+ cp = AddISO8601IntervalPart(cp,hour,'H');
+ cp = AddISO8601IntervalPart(cp,min ,'M');
+ if ((sec != 0) || (fsec != 0))
+ {
+ cp += sprintf(cp, "%d", sec);
+ AppendFsec(cp,fsec);
+ TrimTrailingZeros(cp);
+ cp += strlen(cp);
+ cp += sprintf(cp,"S");
+ }
+ break;
/* compatible with postgresql 8.3 when DateStyle = 'iso' */
case INTSTYLE_POSTGRES:
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
***************
*** 627,632 **** interval_in(PG_FUNCTION_ARGS)
--- 627,635 ----
ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
dterr = DecodeInterval(field, ftype, nf, range, &dtype, tm, &fsec);
+ if (dterr == DTERR_BAD_FORMAT)
+ dterr = DecodeISO8601Interval(str, tm, &fsec);
+
if (dterr != 0)
{
if (dterr == DTERR_FIELD_OVERFLOW)
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 1956,1963 **** psql_completion(char *text, int start, int end)
else if (pg_strcasecmp(prev2_wd, "IntervalStyle") == 0)
{
static const char *const my_list[] =
! {"postgres","postgres_verbose", "sql_standard", NULL};
!
COMPLETE_WITH_LIST(my_list);
}
else if (pg_strcasecmp(prev2_wd, "GEQO") == 0)
--- 1956,1962 ----
else if (pg_strcasecmp(prev2_wd, "IntervalStyle") == 0)
{
static const char *const my_list[] =
! {"postgres","postgres_verbose", "sql_standard", "iso_8601", NULL};
COMPLETE_WITH_LIST(my_list);
}
else if (pg_strcasecmp(prev2_wd, "GEQO") == 0)
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 197,206 **** extern int DateOrder;
--- 197,208 ----
* INTSTYLE_POSTGRES Like Postgres8.3 when DateStyle = 'iso'
* INTSTYLE_POSTGRES_VERBOSE Like Postgres8.3 when DateStyle = 'sql'
* INTSTYLE_SQL_STANDARD SQL standard interval literals
+ * INTSTYLE_ISO_8601 Specifies ISO-8601-basic formatted intervals
*/
#define INTSTYLE_POSTGRES 0
#define INTSTYLE_POSTGRES_VERBOSE 1
#define INTSTYLE_SQL_STANDARD 2
+ #define INTSTYLE_ISO_8601 3
extern int IntervalStyle;
*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 292,297 **** extern int DecodeTimeOnly(char **field, int *ftype,
--- 292,300 ----
extern int DecodeInterval(char **field, int *ftype,
int nf, int range, int *dtype,
struct pg_tm * tm, fsec_t *fsec);
+ extern int DecodeISO8601Interval(char *str,
+ struct pg_tm * tm, fsec_t *fsec);
+
extern void DateTimeParseError(int dterr, const char *str,
const char *datatype);