Brendan Jurd wrote:
...Sep 18, 2008...Ron Mayer <[EMAIL PROTECTED]> wrote:
(1) ...GUC called "IntervalStyle"...
(2) ...interval style that will match the SQL standards...
...an initial review...
When I ran the regression tests, I got one failure in the new interval
Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP.
The C code has some small stylistic inconsistencies; ...
... spaces around binary operators are missing (e.g., "(fsec<0)").
Thanks. Fixed these.
...function calls missing the space after the argument separator...
I think I fixed all these now too.
The new documentation is good in terms of content, but there are some
minor stylistic and spelling cleanups I would suggest.
...variously..."SQL standard", "SQL-standard" and "SQL Standard"...
Got it. There are a few inconsistencies elsewhere in the file
talking about other data types. I wonder if I should fix those
as well.
These sentences in datatype.sgml are a bit awkward ...
I would go with something more along the lines of...
Yes. Thanks for the better wording.
I don't think "old releases" is specific enough.
Yup - fixed that too.
That's all the feedback I have for the moment. I hope you found my
comments helpful. I'll be setting the status of this patch to
"Returned with Feedback" and wait for your responses before I move
forward with reviewing the other patches.
Great. I've tried to update the style on my remaining patches as well.
In addition, I've added to the docs describing how I use
explicit '+' and '-' signs to disambiguate the mixed-sign
non-standard intervals when in the sql_standard mode.
As before the 3 patches are at: http://0ape.com/postgres_interval_patches/
and http://git.forensiclogic.com/postgresql/ and
http://git.forensiclogic.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup
I'm attaching the patch dealing with sql standard intervals here for the
archives.
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4013,4018 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4013,4056 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
+ <term><varname>IntervalStyle</varname> (<type>string</type>)</term>
+ <indexterm>
+ <primary><varname>IntervalStyle</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Sets the display format for interval values.
+ The value <literal>sql_standard</> will produce
+ output matching <acronym>SQL</acronym> standard
+ interval literals for values that conform to the
+ <acronym>SQL</acronym> standard (either year-month
+ only or date-time only; and no mixing of positive
+ and negative components).
+
+ The value <literal>postgres</> will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>ISO</>.
+
+ The value <literal>postgres_verbose</> will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>SQL</>.
+ </para>
+ <para>
+ The IntervalStyle GUC also affects the interpretation
+ of one ambiguous interval literal input. In SQL 2008
+ the negative sign in the interval literal '-1 2:03:04'
+ applies to both the days and hour/minute/second parts.
+ PostgreSQL traditionally only applied the negative
+ sign to the days part. If IntervalStyle is set to
+ <literal>sql_standard</literal> it will follow the standard
+ otherwise it uses the traditional postgres interpretation.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-timezone" xreflabel="timezone">
<term><varname>timezone</varname> (<type>string</type>)</term>
<indexterm>
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 1962,1968 **** January 8 04:05:06 1999 PST
a combination of years and months can be specified with a dash;
for example <literal>'200-10'</> is read the same as <literal>'200 years
10 months'</>. (These shorter forms are in fact the only ones allowed
! by the SQL standard.)
</para>
<para>
--- 1962,1968 ----
a combination of years and months can be specified with a dash;
for example <literal>'200-10'</> is read the same as <literal>'200 years
10 months'</>. (These shorter forms are in fact the only ones allowed
! by the <acronym>SQL</acronym> standard.)
</para>
<para>
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2310 ----
</para>
</sect2>
+ <sect2 id="interval-output">
+ <title>Interval Output</title>
+
+ <indexterm>
+ <primary>interval</primary>
+ <secondary>output format</secondary>
+ <seealso>formatting</seealso>
+ </indexterm>
+
+ <para>
+ The output format of the interval types can be set to one of the
+ three styles <literal>sql_standard</>,
+ <literal>postgres</>, or <literal>postgres_verbose</>.
+ The default is the <literal>postgres</> format.
+ <xref
+ linkend="interval-style-output-table"> shows examples of each
+ output style.
+ </para>
+
+ <para>
+ The <literal>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 <acronym>SQL</acronym> standard).
+ For an interval containing both a year-month and a datetime
+ component, the output will be a <acronym>SQL</acronym> standard
+ unquoted year-month literal string concatenated with a
+ <acronym>SQL</acronym> standard unquoted datetime literal
+ string with a space in between; and '+' and '-' signs added
+ to disambiguate mixed-sign intervals.
+ </para>
+
+ <para>
+ The <literal>postgres</> style will output intervals
+ matching those output by PostgreSQL releases prior to 8.4
+ when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>ISO</>.
+ </para>
+
+ <para>
+ The <literal>postgres_verbose</> style will output intervals
+ matching those output by PostgreSQL releases prior to 8.4
+ when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>SQL</>.
+ </para>
+
+ <table id="interval-style-output-table">
+ <title>Interval Style Example</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Style Specification</entry>
+ <entry>Year-Month Interval</entry>
+ <entry>DateTime Interval</entry>
+ <entry>Nonstandardrd Extended Interval</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>sql_standard</entry>
+ <entry>1-2</entry>
+ <entry>3 4:05:06</entry>
+ <entry>-1-2 +3 -4:05:06</entry>
+ </row>
+ <row>
+ <entry>postgres</entry>
+ <entry>1 year 2 mons</entry>
+ <entry>3 days 04:05:06</entry>
+ <entry>-1 year -2 mons +3 days -04:05:06</entry>
+ </row>
+ <row>
+ <entry>postgres_verbose</entry>
+ <entry>@ 1 year 2 mons</entry>
+ <entry>@ 3 days 4 hours 5 mins 6 secs</entry>
+ <entry>@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Note that <literal>sql_standard</> style will only produce strictly
+ standards-conforming interval literals when given a strictly
+ <acronym>SQL</acronym> standard interval value - meaning that
+ it needs to be a pure year-month or datetime interval and not
+ mix positive and negative components.
+ </para>
+
+ </sect2>
+
+
+
<sect2 id="datatype-timezones">
<title>Time Zones</title>
*** 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/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 2766,2771 **** DecodeInterval(char **field, int *ftype, int nf, int range,
--- 2766,2787 ----
case DTK_TIME:
dterr = DecodeTime(field[i], fmask, range,
&tmask, tm, fsec);
+ if (IntervalStyle == INTSTYLE_SQL_STANDARD &&
+ field[0][0] == '-' && i == 1 &&
+ field[i][0] != '-' && field[i][0] != '+')
+ {
+ /*
+ * The SQL Standard defines the interval literal
+ * '-1 1:00:00'
+ * to mean "negative 1 days and negative one hours"
+ * while Postgres traditionally treated this as
+ * to mean "negative 1 days and positive one hours"
+ */
+ tm->tm_hour = -tm->tm_hour;
+ tm->tm_min = -tm->tm_min;
+ tm->tm_sec = -tm->tm_sec;
+ *fsec = - *fsec;
+ }
if (dterr)
return dterr;
type = DTK_DAY;
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3621,3647 ----
return TRUE;
}
+ /*
+ * Small helper funciton to avoid copy&paste in EncodeInterval below
+ */
+ static void
+ AppendSeconds(char * cp, int sec, fsec_t fsec)
+ {
+ if (fsec == 0)
+ {
+ sprintf(cp, "%02d", abs(sec));
+ }
+ else
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ sprintf(cp, "%02d.%06d", abs(sec), Abs(fsec));
+ #else
+ sprintf(cp, "%012.9f", fabs(sec + 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,
--- 3650,3666 ----
* 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)
--- 3669,3681 ----
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",
--- 3683,3760 ----
*/
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 "<sign>" 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)
! ? '-' : '+';
! cp += sprintf(cp, "%c%d-%d %c%d %c%d:%02d:",
! year_sign, abs(year), abs(mon),
! day_sign, abs(mday),
! sec_sign, abs(hour), abs(min));
! AppendSeconds(cp, sec, fsec);
! }
! else if (has_year_month)
! {
! sprintf(cp, "%d-%d", year, mon);
! }
! else if (has_day)
! {
! cp += sprintf(cp, "%d %d:%02d:", mday, hour, min);
! AppendSeconds(cp, sec, fsec);
! }
! else
! {
! cp += sprintf(cp, "%d:%02d:", hour, min);
! AppendSeconds(cp, sec, fsec);
! }
! 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);
--- 3817,3831 ----
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() */
--- 3952,3970 ----
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, 0, IntervalStyle, 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
***************
*** 366,371 **** static bool session_auth_is_superuser;
--- 366,372 ----
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;
***************
*** 2078,2083 **** static struct config_string ConfigureNamesString[] =
--- 2079,2094 ----
"ISO, MDY", assign_datestyle, NULL
},
+ {
+ {"IntervalStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
+ gettext_noop("Sets the display format for interval values."),
+ gettext_noop(""),
+ 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
***************
*** 419,424 ****
--- 419,425 ----
# - 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 interval literals
+ */
+ #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.
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***************
*** 209,214 **** static const PQEnvironmentOption EnvironmentOptions[] =
--- 209,217 ----
"PGDATESTYLE", "datestyle"
},
{
+ "PGINTERVALSTYLE", "intervalstyle"
+ },
+ {
"PGTZ", "timezone"
},
{
*** a/src/test/regress/expected/interval.out
--- b/src/test/regress/expected/interval.out
***************
*** 2,7 ****
--- 2,8 ----
-- INTERVAL
--
SET DATESTYLE = 'ISO';
+ SET IntervalStyle to postgres;
-- check acceptance of "time zone style"
SELECT INTERVAL '01:00' AS "One hour";
One hour
***************
*** 273,278 **** FROM INTERVAL_MULDIV_TBL;
--- 274,280 ----
DROP TABLE INTERVAL_MULDIV_TBL;
SET DATESTYLE = 'postgres';
+ SET IntervalStyle to postgres_verbose;
SELECT '' AS ten, * FROM INTERVAL_TBL;
ten | f1
-----+-------------------------------
***************
*** 326,331 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 328,334 ----
-- test fractional second input, and detection of duplicate units
SET DATESTYLE = 'ISO';
+ SET IntervalStyle TO postgres;
SELECT '1 millisecond'::interval, '1 microsecond'::interval,
'500 seconds 99 milliseconds 51 microseconds'::interval;
interval | interval | interval
***************
*** 609,611 **** SELECT interval '1 2:03:04.5678' minute to second(2);
--- 612,648 ----
00:03:04.57
(1 row)
+ -- test inputting and outputting SQL standard interval literals
+ SET IntervalStyle TO sql_standard;
+ SELECT interval '0' AS "zero",
+ interval '1-2' year to month AS "year-month",
+ interval '1 2:03:04' day to second AS "day-time",
+ - interval '1-2' AS "negative year-month",
+ - interval '1 2:03:04' AS "negative day-time";
+ zero | year-month | day-time | negative year-month | negative day-time
+ ------+------------+-----------+---------------------+-------------------
+ 0 | 1-2 | 1 2:03:04 | -1-2 | -1 2:03:04
+ (1 row)
+
+ -- test input of some not-quite-standard interval values in the sql style
+ SET IntervalStyle TO postgres;
+ SELECT interval '+1 -1:00:00',
+ interval '-1 +1:00:00',
+ interval '+1-2 -3 +4:05:06.789',
+ interval '-1-2 +3 -4:05:06.789';
+ interval | interval | interval | interval
+ -----------------+-------------------+-------------------------------------+----------------------------------------
+ 1 day -01:00:00 | -1 days +01:00:00 | 1 year 2 mons -3 days +04:05:06.789 | -1 years -2 mons +3 days -04:05:06.789
+ (1 row)
+
+ -- test output of couple non-standard interval values in the sql style
+ SET IntervalStyle TO sql_standard;
+ SELECT interval '1 day -1 hours',
+ interval '-1 days +1 hours',
+ interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
+ - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
+ interval | interval | interval | ?column?
+ ------------------+------------------+----------------------+----------------------
+ +0-0 +1 -1:00:00 | +0-0 -1 +1:00:00 | +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
+ (1 row)
+
*** a/src/test/regress/pg_regress.c
--- b/src/test/regress/pg_regress.c
***************
*** 708,713 **** initialize_environment(void)
--- 708,714 ----
*/
putenv("PGTZ=PST8PDT");
putenv("PGDATESTYLE=Postgres, MDY");
+ putenv("PGINTERVALSTYLE=postgres_verbose");
if (temp_install)
{
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 3,8 ****
--- 3,9 ----
--
SET DATESTYLE = 'ISO';
+ SET IntervalStyle to postgres;
-- check acceptance of "time zone style"
SELECT INTERVAL '01:00' AS "One hour";
***************
*** 94,99 **** FROM INTERVAL_MULDIV_TBL;
--- 95,101 ----
DROP TABLE INTERVAL_MULDIV_TBL;
SET DATESTYLE = 'postgres';
+ SET IntervalStyle to postgres_verbose;
SELECT '' AS ten, * FROM INTERVAL_TBL;
***************
*** 118,123 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 120,127 ----
-- test fractional second input, and detection of duplicate units
SET DATESTYLE = 'ISO';
+ SET IntervalStyle TO postgres;
+
SELECT '1 millisecond'::interval, '1 microsecond'::interval,
'500 seconds 99 milliseconds 51 microseconds'::interval;
SELECT '3 days 5 milliseconds'::interval;
***************
*** 174,176 **** SELECT interval '1 2:03:04.5678' hour to second(2);
--- 178,202 ----
SELECT interval '1 2.3456' minute to second(2);
SELECT interval '1 2:03.5678' minute to second(2);
SELECT interval '1 2:03:04.5678' minute to second(2);
+
+ -- test inputting and outputting SQL standard interval literals
+ SET IntervalStyle TO sql_standard;
+ SELECT interval '0' AS "zero",
+ interval '1-2' year to month AS "year-month",
+ interval '1 2:03:04' day to second AS "day-time",
+ - interval '1-2' AS "negative year-month",
+ - interval '1 2:03:04' AS "negative day-time";
+
+ -- test input of some not-quite-standard interval values in the sql style
+ SET IntervalStyle TO postgres;
+ SELECT interval '+1 -1:00:00',
+ interval '-1 +1:00:00',
+ interval '+1-2 -3 +4:05:06.789',
+ interval '-1-2 +3 -4:05:06.789';
+
+ -- test output of couple non-standard interval values in the sql style
+ SET IntervalStyle TO sql_standard;
+ SELECT interval '1 day -1 hours',
+ interval '-1 days +1 hours',
+ interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
+ - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
