From a4917bef0ead2424bf0c2eeb05dfb681dff33574 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 11 Nov 2008 02:42:33 +0000 Subject: Add support for input and output of interval values formatted per ISO 8601; specifically, we can input either the "format with designators" or the "alternative format", and we can output the former when IntervalStyle is set to iso_8601. Ron Mayer --- doc/src/sgml/config.sgml | 5 +- doc/src/sgml/datatype.sgml | 132 +++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 131 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 715eb44e010..7931ea87377 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -4032,6 +4032,9 @@ SET XML OPTION { DOCUMENT | CONTENT }; matching PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to non-ISO output. + The value iso_8601 will produce output matching the time + interval format with designators defined in section + 4.4.3.2 of ISO 8601. The IntervalStyle parameter also affects the diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index d26fdc5fde6..c9669b49512 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ - + Data Types @@ -2353,9 +2353,9 @@ January 8 04:05:06 1999 PST interval values can be written with the following verbose syntax: - + @ quantity unit quantity unit... direction - + where quantity is a number (possibly signed); unit is microsecond, @@ -2384,6 +2384,76 @@ January 8 04:05:06 1999 PST IntervalStyle is set to sql_standard.) + + Interval values can also be written as ISO 8601 time intervals, using + either the format with designators of the standard's section + 4.4.3.2 or the alternative format of section 4.4.3.3. The + format with designators looks like this: + +P quantity unit quantity unit ... T quantity unit ... + + The string must start with a P, and may include a + T that introduces the time-of-day units. The + available unit abbreviations are given in . Units may be + omitted, and may be specified in any order, but units smaller than + a day must appear after T. In particular, the meaning of + M depends on whether it is before or after + T. + + + + ISO 8601 interval unit abbreviations + + + + Abbreviation + Meaning + + + + + Y + Years + + + M + Months (in the date part) + + + W + Weeks + + + D + Days + + + H + Hours + + + M + Minutes (in the time part) + + + S + Seconds + + + +
+ + + In the alternative format: + +P years-months-days T hours:minutes:seconds + + the string must begin with P, and a + T separates the date and time parts of the interval. + The values are given as numbers similar to ISO 8601 dates. + + When writing an interval constant with a fields specification, or when assigning to an interval column that was defined @@ -2433,6 +2503,46 @@ January 8 04:05:06 1999 PST For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output. + + + shows some examples + of valid interval input. + + + + Interval Input + + + + Example + Description + + + + + 1-2 + SQL standard format: 1 year 2 months + + + 3 4:05:06 + SQL standard format: 3 days 4 hours 5 minutes 6 seconds + + + 1 year 2 months 3 days 4 hours 5 minutes 6 seconds + Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds + + + P1Y2M3DT4H5M6S + ISO 8601 format with designators: same meaning as above + + + P0001-02-03T04:05:06 + ISO 8601 alternative format: same meaning as above + + + +
+ @@ -2446,8 +2556,8 @@ January 8 04:05:06 1999 PST The output format of the interval type can be set to one of the - three styles sql_standard, - postgres, or postgres_verbose, + four styles sql_standard, postgres, + postgres_verbose, or iso_8601, using the command SET intervalstyle. The default is the postgres format. shows examples of each @@ -2476,6 +2586,12 @@ January 8 04:05:06 1999 PST DateStyle parameter was set to non-ISO output. + + The output of the iso_8601 style matches the format + with designators described in section 4.4.3.2 of the + ISO 8601 standard. + + Interval Output Style Examples @@ -2506,6 +2622,12 @@ January 8 04:05:06 1999 PST @ 3 days 4 hours 5 mins 6 secs @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago + + iso_8601 + P1Y2M + P3DT4H5M6S + P-1Y-2M3DT-4H-5M-6S +
-- cgit v1.2.3