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 1sQrm6-00C1q1-Eg for pgsql-bugs@arkaria.postgresql.org; Mon, 08 Jul 2024 17:03:38 +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 1sQrm4-007VWa-F0 for pgsql-bugs@arkaria.postgresql.org; Mon, 08 Jul 2024 17:03:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sQrm4-007VWS-3y for pgsql-bugs@lists.postgresql.org; Mon, 08 Jul 2024 17:03:36 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sQrm1-0014vd-8a for pgsql-bugs@lists.postgresql.org; Mon, 08 Jul 2024 17:03:35 +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 468H3S971079671; Mon, 8 Jul 2024 13:03:28 -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: <476283.1715177448@sss.pgh.pa.us> 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> <166456.1715117228@sss.pgh.pa.us> <476283.1715177448@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 08 May 2024 10:10:48 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1079616.1720458166.0@sss.pgh.pa.us> Date: Mon, 08 Jul 2024 13:03:28 -0400 Message-ID: <1079670.1720458208@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="us-ascii" Content-ID: <1079616.1720458166.1@sss.pgh.pa.us> I took another look at this issue and got annoyed by the fact that the proposed coding for "quarter" still doesn't satisfy the rule that the output for a negative interval should be the negative of the output for the sign-reversed interval. Specifically, if the month field is zero, the v2 patch always emits 1: regression=# select extract(quarter from interval '1 day'); extract --------- 1 (1 row) regression=# select extract(quarter from interval '-1 day'); extract --------- 1 (1 row) We could fix that by examining the sign of the lower-order fields when month is zero, as in the v3 patch attached. However, I'm not at all sure this is really better than v2. Notably, it makes the documentation's statement that the result is "the month field divided by 3 plus 1" even more incomplete. I still don't really want to go into details about the behavior for negative intervals. OTOH if we did do that, I'd rather write a blanket statement about the result being the negative of the result for a positive interval. Thoughts? regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v3-interval-week-and-quarter-fixes.patch"; charset="us-ascii" Content-ID: <1079616.1720458166.2@sss.pgh.pa.us> Content-Description: v3-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 93ee3d4b60..bd430e712d 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 cdc7e43b93..13ddba4674 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,33 @@ 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 the month field is zero, use the + * lower-order fields to decide the sign. + */ + if (interval->month > 0) + intresult =3D (tm->tm_mon / 3) + 1; + else if (interval->month < 0) + intresult =3D -(((-interval->month % MONTHS_PER_YEAR) / 3) + 1); + else if (interval->day > 0 || + (interval->day =3D=3D 0 && interval->time >=3D 0)) + intresult =3D 1; + else + intresult =3D -1; break; = case DTK_YEAR: diff --git a/src/test/regress/expected/interval.out b/src/test/regress/exp= ected/interval.out index 51ae010c7b..a6009f8e6a 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--