Re: BUG #1518: Conversions to (undocumented) SQL year-month and

Roy Badami wrote:
> >>>>> "Bruce" == Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>
> Bruce> OK, here are the TODO items I have created:
>
> Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1
> Bruce> 2:03:04' DAY TO SECOND
>
> Bruce> * Add support for ANSI date INTERVAL
> Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH
>
> You may as well use the correct ANSI terminology:
>
> * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1
> 2:03:04' DAY TO SECOND
>
> * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2'
> YEAR TO MONTH
>
> Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value
> Bruce> to requested precision

OK, I added a new ANSI INTERVAL section to the TODO list:

* Add ANSI INTERVAL handling
o Add support for day-time syntax, INTERVAL '1 2:03:04'
DAY TO SECOND
o Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH
o Process mixed ANSI/PG syntax, and round value to requested
precision or generate an error
o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS
INTERVAL MONTH), and this should return '12 months'
o Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds'

> Never round, I think. Throwing away precision should be an exception,
> unless the discarded fields were zero.

Seems that is implentation-dependent so I added "round' or error" to
the TODO item.

> Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1
> Bruce> year' AS INTERVAL MONTH), and this should return '12
> Bruce> months'
>
> Bruce> Is this sufficient?
>
> You also need to make EXTRACT do the right thing.
>
> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1
>
> but
>
> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13
>
> Ditto for day-time intervals, of course.

Uh, I think this already works fine for PG syntax, and I assume once we
support ANSI syntax it will work fine too:

test=> select EXTRACT (MONTH FROM INTERVAL '1 year 2 month' YEAR TO
MONTH);
date_part
-----------
2
(1 row)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073