Re: Proposed patch: make SQL interval-literal syntax work per spec - Mailing list pgsql-hackers

Tom Lane wrote:
> I think all
> you need is 'yyy-mm dd hh:mm:ss' where you omit yyy-mm if
> zeroes, omit dd if zero, omit hh:mm:ss if zeroes (but maybe
> only if dd is also 0?  otherwise your output is just dd which
> is uncomfortably ambiguous).

Cool.  I think I have it pretty much working with a new
GUC "intervalstyle" that can take values of

"sql_standard" that I think will output SQL standard               interval literals when given a sql
standardinterval.
 

"iso_8601" that will output ISO 8601 "Time Intervals" of           the "format with time-unit deignators", and

"backward_compatible" that will output the same thing           that postgres currently does that depends           on
thevalue of the DateStyle GUC.
 

I'll add the documentation and regression tests and
can submit a patch early next week.   Oh.  One more
question is that under ecpg there seems to be a fair
amount of near-duplicated code (EncodeDateTime,
EncodeInterval) for turning dates and times and
intervals to strings.

Should those ECPG functions be made identical to
the ones in the backend?
Could those somehow share code with the backend for
some of their work?


Anyway - here's a quick test of the
SQL Standard and ISO interval output as it stands
right now...



regression=# drop table test_intervals;
DROP TABLE
regression=# create temporary table test_intervals (i interval);
CREATE TABLE
regression=# insert into test_intervals values
regression-#   ('0 years'),
regression-#   ('1 year 1 month'),
regression-#   ('1 day 2 hours 3 minutes 4 seconds'),
regression-#   ('1 year 1 minute');
INSERT 0 4
regression=#
regression=# insert into test_intervals values
regression-#   ('1-1'),
regression-#   ('1'),
regression-#   (interval '1' year),
regression-#   ('1:00:00'),
regression-#   ('1 1:02:03');
INSERT 0 5
regression=#
regression=# insert into test_intervals values
regression-#   ('P1Y1M'),
regression-#   ('P1DT1H1M1S'),
regression-#   ('PT1S');
INSERT 0 3
regression=#
regression=# set intervalstyle to sql_standard;
SET
regression=# select * from test_intervals;      i
------------- 0 1-1 1 2:3:4 1-0 0 0:1:0 1-1 0:0:1 1-0 1:0:0 1 1:2:3 1-1 1 1:1:1 0:0:1
(12 rows)


regression=#
regression=# set intervalstyle to iso_8601;
SET
regression=# select * from test_intervals;     i
------------ PT0S P1Y1M P1DT2H3M4S P1YT1M P1Y1M PT1S P1Y PT1H P1DT1H2M3S P1Y1M P1DT1H1M1S PT1S
(12 rows)

regression=#
regression=# set intervalstyle to backward_compatible;
SET
regression=# set datestyle to sql;
SET
regression=# select * from test_intervals;               i
------------------------------- @ 0 @ 1 year 1 mon @ 1 day 2 hours 3 mins 4 secs @ 1 year 1 min @ 1 year 1 mon @ 1 sec
@1 year @ 1 hour @ 1 day 1 hour 2 mins 3 secs @ 1 year 1 mon @ 1 day 1 hour 1 min 1 sec @ 1 sec
 
(12 rows)

regression=# set datestyle to iso;
SET
regression=# select * from test_intervals;        i
----------------- 00:00:00 1 year 1 mon 1 day 02:03:04 1 year 00:01:00 1 year 1 mon 00:00:01 1 year 01:00:00 1 day
01:02:031 year 1 mon 1 day 01:01:01 00:00:01
 
(12 rows)