Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

On Fri, Aug 16, 2024 at 12:06:35PM -0400, Tom Lane wrote:
> regression=# select n, extract(quarter from interval '1 mon' * n) from generate_series(-12,12) n;
> n | extract
> -----+---------
> -12 | 1
> -11 | -2
> -10 | -2
> -9 | -2

Wow, that "1" is weird to see.

> which is fine on the positive side but it's hard to describe the
> results for negative months as anything but wacko. The v2 patch
> gives
>
> regression=# select n, extract(quarter from interval '1 mon' * n) from generate_series(-12,12) n;
> n | extract
> -----+---------
> -12 | -1
> -11 | -4
> -10 | -4
> -9 | -4
> -8 | -3
> -7 | -3
> -6 | -3
> -5 | -2
> -4 | -2
> -3 | -2
> -2 | -1
> -1 | -1
> 0 | 1
> 1 | 1
> 2 | 1
> 3 | 2
> 4 | 2
> 5 | 2
> 6 | 3
> 7 | 3
> 8 | 3
> 9 | 4
> 10 | 4
> 11 | 4
> 12 | 1
> (25 rows)
>
> which is a whole lot saner. So let's run with v2.

Yes, that v2 output looks very clean. I had to really dig my head into
this so I am not surprised it was confusing to find the right solution.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.