Interval Output

*** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 4090,4095 **** SET XML OPTION { DOCUMENT | CONTENT }; --- 4090,4117 ---- + + IntervalStyle (string) + + IntervalStyle configuration parameter + + + + Sets the display format for interval values. + The value sql_standard will output SQL Standard + strings when given intervals that conform to the SQL + standard (either year-month only or date-time only; and no + mixing of positive and negative components). + The value postgres will output intervals in + a format that matches what old releases had output when + the DateStyle was set to 'ISO'. + The value postgres_verbose will output intervals in + a format that matches what old releases had output when + the DateStyle was set to 'SQL'. + + + + timezone (string) *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *************** *** 2213,2218 **** January 8 04:05:06 1999 PST --- 2213,2305 ---- + + Interval Output + + + interval + output format + formatting + + + + 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 + output style. + + + + The sql_standard style will output SQL standard + interval literal strings where the value of the interval + value consists of only a year-month component or a datetime + component (as required by the sql standard). For an interval + containing both a year-month and a datetime component, the + output will be a SQL Standard unquoted year-month literal + string joined to a SQL Standard unquoted datetime literal + string with a space in between. + + + + The postgres style will output intervals that match + the style PostgreSQL 8.3 outputed when the + parameter was set to ISO. + + + + The postgres_verbose style will output intervals that match + the style PostgreSQL 8.3 outputed when the + parameter was set to SQL. + + + + Interval Style Example + + + + Style Specification + Year-Month Interval + DateTime Interval + Nonstandardrd Extended Interval + + + + + sql_standard + 1-2 + 3 4:05:06 + -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 + @ 1 year 2 mons + @ 3 days 4 hours 5 mins 6 secs + @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago + + + +

+ + + 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. + + + + + + Time Zones *** a/src/backend/commands/variable.c --- b/src/backend/commands/variable.c *************** *** 229,234 **** assign_datestyle(const char *value, bool doit, GucSource source) --- 229,271 ---- /* + * assign_intervalstyle: GUC assign_hook for datestyle + */ + const char * + assign_intervalstyle(const char *value, bool doit, GucSource source) + { + int newIntervalStyle = IntervalStyle; + char * result = (char *) malloc(32); + if (pg_strcasecmp(value, "postgres") == 0) + { + newIntervalStyle = INTSTYLE_POSTGRES; + } + else if (pg_strcasecmp(value, "postgres_verbose") == 0) + { + newIntervalStyle = INTSTYLE_POSTGRES_VERBOSE; + } + else if (pg_strcasecmp(value, "sql_standard") == 0) + { + newIntervalStyle = INTSTYLE_SQL_STANDARD; + } + else + { + ereport(GUC_complaint_elevel(source), + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unrecognized \"intervalstyle\" key word: \"%s\"", + value))); + return NULL; + } + if (doit) + { + IntervalStyle = newIntervalStyle; + strcpy(result, "ISO"); + } + return result; + } + + + /* * TIMEZONE */ *** a/src/backend/utils/adt/datetime.c --- b/src/backend/utils/adt/datetime.c *************** *** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, --- 3605,3624 ---- return TRUE; } + /* + * small helper funciton to avoid copy&paste of this ifdef below + */ + void + AppendFsec(char * cp,fsec_t fsec) { + if (fsec==0) return; + #ifdef HAVE_INT64_TIMESTAMP + sprintf(cp, ".%06d", Abs(fsec)); + #else + sprintf(cp, ":%012.9f", fabs(fsec)); + #endif + TrimTrailingZeros(cp); + } + /* EncodeInterval() * Interpret time structure as a delta time and convert to string. *************** *** 3613,3618 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, --- 3627,3643 ---- * 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 + * but useful for exchanging data with computers instead of humans. + * - ron 2003-07-14 + * + * And ISO's SQL 2008 standard specifies standards for + * "year-month literal"s (that look like '2-3') and + * "day-time literal"s (that look like ('4 5:6:7') */ int EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) *************** *** 3621,3626 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) --- 3646,3658 ---- bool is_nonzero = FALSE; char *cp = str; + int year = tm->tm_year; + int mon = tm->tm_mon; + int mday = tm->tm_mday; + int hour = tm->tm_hour; + int min = tm->tm_min; + int sec = tm->tm_sec; + /* * The sign of year and month are guaranteed to match, since they are * stored internally as "month". But we'll need to check for is_before and *************** *** 3628,3635 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) */ switch (style) { ! /* compatible with ISO date formats */ ! case USE_ISO_DATES: if (tm->tm_year != 0) { sprintf(cp, "%d year%s", --- 3660,3738 ---- */ switch (style) { ! /* SQL Standard interval literals */ ! case INTSTYLE_SQL_STANDARD: ! { ! bool has_negative = (year < 0) || (mon < 0) || ! (mday < 0) || (hour < 0) || ! (min < 0) || (sec < 0) || (fsec<0); ! bool has_positive = (year > 0) || (mon > 0) || ! (mday > 0) || (hour > 0) || ! (min > 0) || (sec > 0) || (fsec>0); ! bool has_year_month = (year != 0) || (mon != 0); ! bool has_datetime = (hour != 0) || (min != 0) || ! (sec != 0) || (fsec!= 0) || (mday != 0); ! bool has_day = (mday != 0); ! bool sql_standard_value = (!(has_negative && has_positive)) && ! (!(has_year_month && has_datetime)); ! /* ! * SQL Standard wants only 1 "" preceeding the whole ! * interval. ! */ ! if (has_negative && sql_standard_value) ! { ! sprintf(cp,"-"); ! cp++; ! year = -year; ! mon = -mon; ! mday = -mday; ! hour = -hour; ! min = -min; ! sec = -sec; ! fsec = -fsec; ! } ! if (!has_negative && !has_positive) ! { ! sprintf(cp,"0"); ! } ! else if (!sql_standard_value) ! { ! /* ! * For non sql-standard interval values, ! * force outputting the signs to avoid ! * ambiguities with intervals with mixed ! * sign components. ! */ ! char year_sign = (year<0 || mon<0) ? '-' : '+'; ! char day_sign = (mday<0) ? '-' : '+'; ! char sec_sign = (hour<0 || min<0 || sec<0 || fsec<0) ! ? '-' : '+'; ! sprintf(cp,"%c%d-%d %c%d %c%d:%02d:%02d", ! year_sign,abs(year),abs(mon), ! day_sign,abs(mday), ! sec_sign,abs(hour),abs(min),abs(sec)); ! AppendFsec(cp+strlen(cp),fsec); ! } ! else if (has_year_month) ! { ! sprintf(cp,"%d-%d",year,mon); ! } ! else if (has_day) ! { ! sprintf(cp,"%d %d:%02d:%02d",mday,hour,min,sec); ! AppendFsec(cp+strlen(cp),fsec); ! } ! else ! { ! sprintf(cp,"%d:%02d:%02d",hour,min,sec); ! AppendFsec(cp+strlen(cp),fsec); ! } ! cp += strlen(cp); ! break; ! } ! ! /* compatible with postgresql 8.3 when DateStyle = 'iso' */ ! case INTSTYLE_POSTGRES: if (tm->tm_year != 0) { sprintf(cp, "%d year%s", *************** *** 3692,3700 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) cp += strlen(cp); } } break; ! case USE_POSTGRES_DATES: default: strcpy(cp, "@ "); cp += strlen(cp); --- 3795,3809 ---- cp += strlen(cp); } } + if (!is_nonzero) + { + strcat(cp, "0"); + cp += strlen(cp); + } break; ! /* compatible with postgresql 8.3 when DateStyle = 'sql' */ ! case INTSTYLE_POSTGRES_VERBOSE: default: strcpy(cp, "@ "); cp += strlen(cp); *************** *** 3821,3842 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) is_before = (tm->tm_sec < 0); is_nonzero = TRUE; } break; } - /* identically zero? then put in a unitless zero... */ - if (!is_nonzero) - { - strcat(cp, "0"); - cp += strlen(cp); - } - - if (is_before && (style != USE_ISO_DATES)) - { - strcat(cp, " ago"); - cp += strlen(cp); - } - return 0; } /* EncodeInterval() */ --- 3930,3948 ---- is_before = (tm->tm_sec < 0); is_nonzero = TRUE; } + if (!is_nonzero) + { + strcat(cp, "0"); + cp += strlen(cp); + } + if (is_before) + { + strcat(cp, " ago"); + cp += strlen(cp); + } break; } return 0; } /* EncodeInterval() */ *** a/src/backend/utils/adt/nabstime.c --- b/src/backend/utils/adt/nabstime.c *************** *** 671,677 **** reltimeout(PG_FUNCTION_ARGS) char buf[MAXDATELEN + 1]; reltime2tm(time, tm); ! EncodeInterval(tm, 0, DateStyle, buf); result = pstrdup(buf); PG_RETURN_CSTRING(result); --- 671,677 ---- char buf[MAXDATELEN + 1]; reltime2tm(time, tm); ! EncodeInterval(tm, INTSTYLE_POSTGRES, DateStyle, buf); result = pstrdup(buf); PG_RETURN_CSTRING(result); *** a/src/backend/utils/adt/timestamp.c --- b/src/backend/utils/adt/timestamp.c *************** *** 677,683 **** interval_out(PG_FUNCTION_ARGS) if (interval2tm(*span, tm, &fsec) != 0) elog(ERROR, "could not convert interval to tm"); ! if (EncodeInterval(tm, fsec, DateStyle, buf) != 0) elog(ERROR, "could not format interval"); result = pstrdup(buf); --- 677,683 ---- if (interval2tm(*span, tm, &fsec) != 0) elog(ERROR, "could not convert interval to tm"); ! if (EncodeInterval(tm, fsec, IntervalStyle, buf) != 0) elog(ERROR, "could not format interval"); result = pstrdup(buf); *** a/src/backend/utils/init/globals.c --- b/src/backend/utils/init/globals.c *************** *** 88,93 **** bool ExitOnAnyError = false; --- 88,94 ---- int DateStyle = USE_ISO_DATES; int DateOrder = DATEORDER_MDY; + int IntervalStyle = INTSTYLE_POSTGRES; bool HasCTZSet = false; int CTimeZone = 0; *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *************** *** 367,372 **** static bool session_auth_is_superuser; --- 367,373 ---- static double phony_random_seed; static char *client_encoding_string; static char *datestyle_string; + static char *intervalstyle_string; static char *locale_collate; static char *locale_ctype; static char *server_encoding_string; *************** *** 2098,2103 **** static struct config_string ConfigureNamesString[] = --- 2099,2113 ---- "ISO, MDY", assign_datestyle, NULL }, + { + {"IntervalStyle", PGC_USERSET, CLIENT_CONN_LOCALE, + gettext_noop("Sets the display format for interval values."), + GUC_LIST_INPUT | GUC_REPORT + }, + &intervalstyle_string, + "postgres", assign_intervalstyle, NULL + }, + { {"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Sets the default tablespace to create tables and indexes in."), *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *************** *** 426,431 **** --- 426,432 ---- # - Locale and Formatting - #datestyle = 'iso, mdy' + #intervalstyle = 'postgres' #timezone = unknown # actually, defaults to TZ environment # setting #timezone_abbreviations = 'Default' # Select the set of available time zone *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *************** *** 1953,1958 **** psql_completion(char *text, int start, int end) --- 1953,1965 ---- COMPLETE_WITH_LIST(my_list); } + 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) { static const char *const my_list[] = *** a/src/include/commands/variable.h --- b/src/include/commands/variable.h *************** *** 15,20 **** --- 15,22 ---- extern const char *assign_datestyle(const char *value, bool doit, GucSource source); + extern const char *assign_intervalstyle(const char *value, + bool doit, GucSource source); extern const char *assign_timezone(const char *value, bool doit, GucSource source); extern const char *show_timezone(void); *** a/src/include/miscadmin.h --- b/src/include/miscadmin.h *************** *** 191,196 **** extern PGDLLIMPORT Oid MyDatabaseTableSpace; --- 191,208 ---- extern int DateStyle; extern int DateOrder; + + /* + * IntervalStyles + * INTSTYLE_POSTGRES Like Postgres8.3 when DateStyle = 'iso' + * INTSTYLE_POSTGRES_VERBOSE Like Postgres8.3 when DateStyle = 'sql' + * INTSTYLE_SQL_STANDARD SQL standard interals + */ + #define INTSTYLE_POSTGRES 0 + #define INTSTYLE_POSTGRES_VERBOSE 1 + #define INTSTYLE_SQL_STANDARD 2 + + extern int IntervalStyle; /* * HasCTZSet is true if user has set timezone as a numeric offset from UTC.