Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4SLN-0048jp-7D for pgsql-bugs@arkaria.postgresql.org; Tue, 07 May 2024 21:27:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s4SLK-005M2F-2b for pgsql-bugs@arkaria.postgresql.org; Tue, 07 May 2024 21:27:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4SLJ-005M27-Hu for pgsql-bugs@lists.postgresql.org; Tue, 07 May 2024 21:27:22 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4SLD-001vjD-4s for pgsql-bugs@lists.postgresql.org; Tue, 07 May 2024 21:27:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 447LR8i9166457; Tue, 7 May 2024 17:27:08 -0400 From: Tom Lane To: jian he cc: Francisco Olarte , Michael Bondarenko , pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com, Peter Eisentraut Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); In-reply-to: References: <18348-b097a3587dfde8a4@postgresql.org> <3709307.1708201825@sss.pgh.pa.us> <3749306.1708222748@sss.pgh.pa.us> <222870.1708458150@sss.pgh.pa.us> <270582.1708462564@sss.pgh.pa.us> Comments: In-reply-to jian he message dated "Thu, 29 Feb 2024 18:30:17 +0800" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <166420.1715117196.0@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Tue, 07 May 2024 17:27:08 -0400 Message-ID: <166456.1715117228@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="UTF-8" Content-ID: <166420.1715117196.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit jian he writes: > On Wed, Feb 21, 2024 at 4:56 AM Tom Lane 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v2-interval-week-and-quarter-fixes.patch"; charset="us-ascii" Content-ID: <166420.1715117196.2@sss.pgh.pa.us> Content-Description: v2-interval-week-and-quarter-fixes.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 17c44bc338..e7792fbae1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10392,12 +10392,16 @@ SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 m= onths'); quarter - The quarter of the year (1–4) that the date is in + The quarter of the year (1–4) that the date is in; + for interval values, the month field divided by 3 + plus 1 = SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 1 +SELECT EXTRACT(QUARTER FROM INTERVAL '1 year 6 months'); +Result: 3 @@ -10468,9 +10472,16 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); week to get consistent results. = + + For interval values, the week field is simply the nu= mber + of integral days divided by 7. + + SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 +SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours'); +Result: 1 diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/tim= estamp.c index e4715605a2..3e65046117 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -5918,6 +5918,7 @@ NonFiniteIntervalPart(int type, int unit, char *lowu= nits, bool isNegative) case DTK_MILLISEC: case DTK_SECOND: case DTK_MINUTE: + case DTK_WEEK: case DTK_MONTH: case DTK_QUARTER: return 0.0; @@ -6037,12 +6038,27 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnum= eric) intresult =3D tm->tm_mday; break; = + case DTK_WEEK: + intresult =3D tm->tm_mday / 7; + break; + case DTK_MONTH: intresult =3D tm->tm_mon; break; = case DTK_QUARTER: - intresult =3D (tm->tm_mon / 3) + 1; + + /* + * We want to maintain the rule that a field extracted from a + * negative interval is the negative of the field's value for + * the sign-reversed interval. The broken-down tm_year and + * tm_mon aren't very helpful for that, so work from + * interval->month. + */ + if (interval->month >=3D 0) + intresult =3D (tm->tm_mon / 3) + 1; + else + intresult =3D -(((-interval->month % MONTHS_PER_YEAR) / 3) + 1); break; = case DTK_YEAR: diff --git a/src/test/regress/expected/interval.out b/src/test/regress/exp= ected/interval.out index 51ae010c7b..e5d919d0cf 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -1834,6 +1834,7 @@ SELECT f1, EXTRACT(MINUTE FROM f1) AS MINUTE, EXTRACT(HOUR FROM f1) AS HOUR, EXTRACT(DAY FROM f1) AS DAY, + EXTRACT(WEEK FROM f1) AS WEEK, EXTRACT(MONTH FROM f1) AS MONTH, EXTRACT(QUARTER FROM f1) AS QUARTER, EXTRACT(YEAR FROM f1) AS YEAR, @@ -1842,20 +1843,52 @@ SELECT f1, EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM, EXTRACT(EPOCH FROM f1) AS EPOCH FROM INTERVAL_TBL; - f1 | microsecond | millisecond | second |= minute | hour | day | month | quarter | year | decade = | century | millennium | epoch = --------------------------------+-------------+-------------+------------+= --------+-----------+-----------+-------+---------+-----------+-----------= +-----------+------------+------------------- - @ 1 min | 0 | 0.000 | 0.000000 |= 1 | 0 | 0 | 0 | 1 | 0 | 0 = | 0 | 0 | 60.000000 - @ 5 hours | 0 | 0.000 | 0.000000 |= 0 | 5 | 0 | 0 | 1 | 0 | 0 = | 0 | 0 | 18000.000000 - @ 10 days | 0 | 0.000 | 0.000000 |= 0 | 0 | 10 | 0 | 1 | 0 | 0 = | 0 | 0 | 864000.000000 - @ 34 years | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 1 | 34 | 3 = | 0 | 0 | 1072958400.000000 - @ 3 mons | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 3 | 2 | 0 | 0 = | 0 | 0 | 7776000.000000 - @ 14 secs ago | -14000000 | -14000.000 | -14.000000 |= 0 | 0 | 0 | 0 | 1 | 0 | 0 = | 0 | 0 | -14.000000 - @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 |= 3 | 2 | 1 | 0 | 1 | 0 | 0 = | 0 | 0 | 93784.000000 - @ 6 years | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 1 | 6 | 0 = | 0 | 0 | 189345600.000000 - @ 5 mons | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 5 | 2 | 0 | 0 = | 0 | 0 | 12960000.000000 - @ 5 mons 12 hours | 0 | 0.000 | 0.000000 |= 0 | 12 | 0 | 5 | 2 | 0 | 0 = | 0 | 0 | 13003200.000000 - infinity | | | |= | Infinity | Infinity | | | Infinity | Infinity = | Infinity | Infinity | Infinity - -infinity | | | |= | -Infinity | -Infinity | | | -Infinity | -Infinity = | -Infinity | -Infinity | -Infinity + f1 | microsecond | millisecond | second |= minute | hour | day | week | month | quarter | year | de= cade | century | millennium | epoch = +-------------------------------+-------------+-------------+------------+= --------+-----------+-----------+------+-------+---------+-----------+----= -------+-----------+------------+------------------- + @ 1 min | 0 | 0.000 | 0.000000 |= 1 | 0 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | 60.000000 + @ 5 hours | 0 | 0.000 | 0.000000 |= 0 | 5 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | 18000.000000 + @ 10 days | 0 | 0.000 | 0.000000 |= 0 | 0 | 10 | 1 | 0 | 1 | 0 | = 0 | 0 | 0 | 864000.000000 + @ 34 years | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 0 | 1 | 34 | = 3 | 0 | 0 | 1072958400.000000 + @ 3 mons | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 3 | 2 | 0 | = 0 | 0 | 0 | 7776000.000000 + @ 14 secs ago | -14000000 | -14000.000 | -14.000000 |= 0 | 0 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | -14.000000 + @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 |= 3 | 2 | 1 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | 93784.000000 + @ 6 years | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 0 | 1 | 6 | = 0 | 0 | 0 | 189345600.000000 + @ 5 mons | 0 | 0.000 | 0.000000 |= 0 | 0 | 0 | 0 | 5 | 2 | 0 | = 0 | 0 | 0 | 12960000.000000 + @ 5 mons 12 hours | 0 | 0.000 | 0.000000 |= 0 | 12 | 0 | 0 | 5 | 2 | 0 | = 0 | 0 | 0 | 13003200.000000 + infinity | | | |= | Infinity | Infinity | | | | Infinity | In= finity | Infinity | Infinity | Infinity + -infinity | | | |= | -Infinity | -Infinity | | | | -Infinity | -In= finity | -Infinity | -Infinity | -Infinity +(12 rows) + +SELECT -f1, + EXTRACT(MICROSECOND FROM -f1) AS MICROSECOND, + EXTRACT(MILLISECOND FROM -f1) AS MILLISECOND, + EXTRACT(SECOND FROM -f1) AS SECOND, + EXTRACT(MINUTE FROM -f1) AS MINUTE, + EXTRACT(HOUR FROM -f1) AS HOUR, + EXTRACT(DAY FROM -f1) AS DAY, + EXTRACT(WEEK FROM -f1) AS WEEK, + EXTRACT(MONTH FROM -f1) AS MONTH, + EXTRACT(QUARTER FROM -f1) AS QUARTER, + EXTRACT(YEAR FROM -f1) AS YEAR, + EXTRACT(DECADE FROM -f1) AS DECADE, + EXTRACT(CENTURY FROM -f1) AS CENTURY, + EXTRACT(MILLENNIUM FROM -f1) AS MILLENNIUM, + EXTRACT(EPOCH FROM -f1) AS EPOCH + FROM INTERVAL_TBL; + ?column? | microsecond | millisecond | second = | minute | hour | day | week | month | quarter | year | = decade | century | millennium | epoch = +-----------------------------------+-------------+-------------+---------= --+--------+-----------+-----------+------+-------+---------+-----------+-= ----------+-----------+------------+-------------------- + @ 1 min ago | 0 | 0.000 | 0.00000= 0 | -1 | 0 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | -60.000000 + @ 5 hours ago | 0 | 0.000 | 0.00000= 0 | 0 | -5 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | -18000.000000 + @ 10 days ago | 0 | 0.000 | 0.00000= 0 | 0 | 0 | -10 | -1 | 0 | 1 | 0 | = 0 | 0 | 0 | -864000.000000 + @ 34 years ago | 0 | 0.000 | 0.00000= 0 | 0 | 0 | 0 | 0 | 0 | -1 | -34 | = -3 | 0 | 0 | -1072958400.000000 + @ 3 mons ago | 0 | 0.000 | 0.00000= 0 | 0 | 0 | 0 | 0 | -3 | -2 | 0 | = 0 | 0 | 0 | -7776000.000000 + @ 14 secs | 14000000 | 14000.000 | 14.00000= 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | 14.000000 + @ 1 day 2 hours 3 mins 4 secs ago | -4000000 | -4000.000 | -4.00000= 0 | -3 | -2 | -1 | 0 | 0 | 1 | 0 | = 0 | 0 | 0 | -93784.000000 + @ 6 years ago | 0 | 0.000 | 0.00000= 0 | 0 | 0 | 0 | 0 | 0 | -1 | -6 | = 0 | 0 | 0 | -189345600.000000 + @ 5 mons ago | 0 | 0.000 | 0.00000= 0 | 0 | 0 | 0 | 0 | -5 | -2 | 0 | = 0 | 0 | 0 | -12960000.000000 + @ 5 mons 12 hours ago | 0 | 0.000 | 0.00000= 0 | 0 | -12 | 0 | 0 | -5 | -2 | 0 | = 0 | 0 | 0 | -13003200.000000 + -infinity | | | = | | -Infinity | -Infinity | | | | -Infinity | = -Infinity | -Infinity | -Infinity | -Infinity + infinity | | | = | | Infinity | Infinity | | | | Infinity | = Infinity | Infinity | Infinity | Infinity (12 rows) = SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/inte= rval.sql index fbf6e064d6..55054ae65d 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -592,6 +592,7 @@ SELECT f1, EXTRACT(MINUTE FROM f1) AS MINUTE, EXTRACT(HOUR FROM f1) AS HOUR, EXTRACT(DAY FROM f1) AS DAY, + EXTRACT(WEEK FROM f1) AS WEEK, EXTRACT(MONTH FROM f1) AS MONTH, EXTRACT(QUARTER FROM f1) AS QUARTER, EXTRACT(YEAR FROM f1) AS YEAR, @@ -601,6 +602,23 @@ SELECT f1, EXTRACT(EPOCH FROM f1) AS EPOCH FROM INTERVAL_TBL; = +SELECT -f1, + EXTRACT(MICROSECOND FROM -f1) AS MICROSECOND, + EXTRACT(MILLISECOND FROM -f1) AS MILLISECOND, + EXTRACT(SECOND FROM -f1) AS SECOND, + EXTRACT(MINUTE FROM -f1) AS MINUTE, + EXTRACT(HOUR FROM -f1) AS HOUR, + EXTRACT(DAY FROM -f1) AS DAY, + EXTRACT(WEEK FROM -f1) AS WEEK, + EXTRACT(MONTH FROM -f1) AS MONTH, + EXTRACT(QUARTER FROM -f1) AS QUARTER, + EXTRACT(YEAR FROM -f1) AS YEAR, + EXTRACT(DECADE FROM -f1) AS DECADE, + EXTRACT(CENTURY FROM -f1) AS CENTURY, + EXTRACT(MILLENNIUM FROM -f1) AS MILLENNIUM, + EXTRACT(EPOCH FROM -f1) AS EPOCH + FROM INTERVAL_TBL; + SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error = ------- =_aaaaaaaaaa0--