From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Francisco Olarte <folarte@peoplecall.com>
Cc: Michael Bondarenko <work.michael.2956@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: dgrowleyml@gmail.com <dgrowleyml@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Date: Tue, 7 May 2024 22:09:34 +0000
Message-ID: <2219fd69a3024149af5adc31e14b25d7@evernorth.com> (raw)
In-Reply-To: <166456.1715117228@sss.pgh.pa.us>
References: <18348-b097a3587dfde8a4@postgresql.org>
<CAApHDvr_-05ce6FaLM0P=hJBdV=Ht3=sdk_Eq__7YOf+fs5x9g@mail.gmail.com>
<CACJufxHYQSKNnt8rS7GxvK04wf75nw_ugMaibFVVfrcdVMmF1Q@mail.gmail.com>
<CAF_O+z1FU5GN=LnW50QjANGVB8+6VxyK1JHa8fE-Xs205MFuqw@mail.gmail.com>
<CA+bJJbw6q=6no1Hf67FraYGMo12asWCa7yw=J0zg_ELbivX0gw@mail.gmail.com>
<3709307.1708201825@sss.pgh.pa.us>
<CACJufxGB4MD6vRz3jVGK6kza-6fuDgfW2jC_1S2+MwcQYGkgSA@mail.gmail.com>
<3749306.1708222748@sss.pgh.pa.us>
<CACJufxHL0g73FPaRS+OGSYWJ0YbP6nEdVK79=wjL-TKU0_heDw@mail.gmail.com>
<222870.1708458150@sss.pgh.pa.us>
<270582.1708462564@sss.pgh.pa.us>
<CACJufxFOjGpAQJ5EJ0=a1geERgdHW77SEdRyK8A_qwB3CVdwhQ@mail.gmail.com>
<166456.1715117228@sss.pgh.pa.us>
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->month if 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Matthew.Wetmore@evernorth.com, tgl@sss.pgh.pa.us, folarte@peoplecall.com, work.michael.2956@gmail.com, pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com, peter.eisentraut@enterprisedb.com
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
In-Reply-To: <2219fd69a3024149af5adc31e14b25d7@evernorth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox