From: | Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle |
Date: | 2008-09-17 19:03:13 |
Message-ID: | 48D15471.6080305@cheapcomplexdevices.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
> In fact, given that we are now
> somewhat SQL-compliant on interval input, a GUC that selected
> PG traditional, SQL-standard, or ISO 8601 interval output format seems
> like it could be a good idea.
Short summary:
The attached patch
(1) adds a new GUC called "IntervalStyle" that decouples interval
output from the "DateStyle" GUC, and
(2) adds a new interval style that will match the SQL standards
for interval literals when given interval data that meets the
sql standard (year-month or date-time only; and no mixed sign).
Background:
Currently Postgres outputs Intervals in one of two formats
depending on DateStyle. When DateStyle is 'ISO', it outputs
intervals like '1 year 2 mons 3 days -04:05:06.78' (though I
know of no ISO interval standards that look like that). When
DateStyle is 'SQL' it outputs intervals like
'@ 1 year 2 mons 3 days -4 hours -5 mins -6.78 secs' (though
I know of no SQL interval standards that look like that).
The feature:
The SQL standard only specifies interval literal strings
for the two kinds of intervals (year-month and day-time)
that are defined by the SQL spec. It also doesn't account
for postgres's intervals that can have mixed-sign components.
I tried to make the output here be a logical extension
of the spec, concatenating the year-month and day-time
interval strings and forcing signs in the output that could
otherwise have been ambiguous.
This little table shows an example of the output of this
new style compared to the existing postgres output for
(a) a year-month interval, (b) a day-time interval, and
(c) a not-quite-standard postgres interval with year-month
and day-time components of varying signs.
'1-2' | '@ 1 year 2 mons'
'3 4:05:06.78' | '@ 3 days 4 hours 5 mins 6.78 secs'
'+1-2 -3 +4:05:06.78' | '@ 1 year 2 mons -3 days 4 hours 5 mins 6.78 secs'
The patch:
Seems to work for me; and I believe I updated the docs.
Many regression tests fail, though, because they assume
the existing coupling of DateStyle and interval output
styles. If people like where this is going I can update
those regression tests and add ones to test this new style.
Attachment | Content-Type | Size |
---|---|---|
sql_standard_interval_output.patch | text/x-diff | 15.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2008-09-17 20:21:49 | Re: Autovacuum and Autoanalyze |
Previous Message | Simon Riggs | 2008-09-17 17:44:54 | Re: Autovacuum and Autoanalyze |