Skip site navigation (1) Skip section navigation (2)

Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle

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 (view raw or flat)
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: sql_standard_interval_output.patch
Description: text/x-diff (15.8 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2008-09-17 20:21:49
Subject: Re: Autovacuum and Autoanalyze
Previous:From: Simon RiggsDate: 2008-09-17 17:44:54
Subject: Re: Autovacuum and Autoanalyze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group