BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); - Mailing list pgsql-bugs

Devils advocating here, feel free to ignore.

Is there a real need for a negative month?  Sounds like high level this could be disastrous if I screw up the syntax.
(Ah,memories of DD)
 

I have done this in data warehousing with dimensions tables.

Just process on the INT and translate into the name.

I was thinking on how a negative month could impact this side (data warehousing) side of querying. 

I could be chicken little on this, but wanted it in the conversation.

workaround for negative months:

CREATE TABLE dim_biz_hours( year INT(4)
, doy INT(3)
, dow INT(7)
, month INT(2)
, day INT(2)
, hour INT(2)
, minute INT(2)
, second INT(2)
, utc_offset INT(2)
, utc_offset_dst INT(2)
);

INSERT INTO biz_hours (year)
SELECT * FROM generate_series(2000, 2099);

INSERT INTO biz_hours (doy)
SELECT * FROM generate_series(1, 366);

INSERT INTO biz_hours (dow)
SELECT * FROM generate_series(1, 7);

INSERT INTO biz_hours (month)
SELECT * FROM generate_series(1, 12);

INSERT INTO biz_hours (day)
SELECT * FROM generate_series(1, 31) ;

INSERT INTO biz_hours (hour)
SELECT * FROM generate_series(1, 24);

INSERT INTO biz_hours (minute)
SELECT * FROM generate_series(1, 60);

INSERT INTO biz_hours (second
SELECT * FROM generate_series(1, 60);

INSERT INTO biz_hours (utc_offset)
SELECT * FROM generate_series(1, 24);

INSERT INTO biz_hours (utc_offset_dst)
SELECT * FROM generate_series(1, 24);


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Tuesday, May 7, 2024 2:27 PM
To: jian he <jian.universality@gmail.com>
Cc: Francisco Olarte <folarte@peoplecall.com>; Michael Bondarenko <work.michael.2956@gmail.com>;
pgsql-bugs@lists.postgresql.org;dgrowleyml@gmail.com; Peter Eisentraut <peter.eisentraut@enterprisedb.com>
 
Subject: [EXTERNAL] Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

jian he <jian.universality@gmail.com> writes:
> On Wed, Feb 21, 2024 at 4:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Yeah, I see what you mean: the output for negative month counts is 
>>> very bizarre, whereas other fields seem to all produce the negative 
>>> of what they'd produce for the absolute value of the interval.
>>> We could either try to fix that or decide that rejecting "quarter"
>>> for intervals is the saner answer.

>> After fooling with these cases for a little I'm inclined to think we 
>> should do it as attached (no test or docs changes yet).

> ... I don't know how to write the documentation for the `quarter` when 
> it's negative.

After poking at it some more, I realized that my draft patch was still wrong about that.  We really have to look at
interval->monthif we want to behave plausibly for negative months.
 

Here's a more fleshed-out patch.  I don't think we really need to document the behavior for negative intervals; at
least,we haven't done that so far for any other fields.  I did add testing of such cases though.
 

            regards, tom lane