Re: Proposed patch: make SQL interval-literal syntax work per spec

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
standard interval.

"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 the value 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:03
1 year 1 mon
1 day 01:01:01
00:00:01
(12 rows)