From 6be39d77a70df52d5a0f2eb414ef9901ccf17e5a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 16 Aug 2024 12:35:50 -0400 Subject: Fix extraction of week and quarter fields from intervals. "EXTRACT(WEEK FROM interval_value)" formerly threw an error. Define it as "tm->tm_mday / 7". (With C99 division semantics, this gives consistent results for negative intervals.) "EXTRACT(QUARTER FROM interval_value)" has been implemented all along, but it formerly gave extremely strange results for negative intervals. Fix it so that the output for -N months is the negative of the output for N months. Per bug #18348 from Michael Bondarenko and subsequent discussion. Discussion: https://postgr.es/m/18348-b097a3587dfde8a4@postgresql.org --- doc/src/sgml/func.sgml | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5dd95d73a1a..749360a4b71 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10432,12 +10432,16 @@ SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); 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 @@ -10508,9 +10512,16 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); week to get consistent results. + + For interval values, the week field is simply the number + 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 -- cgit v1.2.3