[HACKERS] Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer wrote:
Ron Mayer wrote:
Tom Lane wrote:
...GUC that selected PG traditional, SQL-standard... interval output
format seems like it could be a good idea.
This is an update to the earlier SQL-standard-interval-literal output
patch that I submitted here:
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
Yet another update - mostly bringing the patch current with HEAD
now that the previous commit fest is over; and also posting it again
since I have a followup patch (for ISO 8601 interval input and output)
that is based on this one and I want the patches lines-of-code to match.
Ron
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 4016,4021 SET XML OPTION { DOCUMENT | CONTENT };
--- 4016,4043
+
+ 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, value);
+ }
+ return result;
+ }
+
+
+ /*
* TIMEZONE
*/
*** a/sr
[HACKERS] Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer wrote:
Tom Lane wrote:
...GUC that selected PG traditional, SQL-standard... interval output
format seems like it could be a good idea.
This is an update to the earlier SQL-standard-interval-literal output
patch that I submitted here:
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
This version fixes a couple bugs in my last patch related to reltime output and
with the new GUC variable, and updated the regression tests to adjust the
new IntervalStyle guc to match the output of the previous regression tests
where the interval output depended on DateStyle.
I've also added it to the Nov CommitFest wiki page.
*** 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, value);
+ }
+ return result;
+ }
+
+
+ /*
