Thread: BUG #3624: EXTRACT(QUARTER FROM INTERVAL) always returns 1

The following bug has been logged online:

Bug reference:      3624
Logged by:          Eugene M. Hutorny
Email address:      eugene@ksf.kiev.ua
PostgreSQL version: 8.2.4
Operating system:   freebsd6.2
Description:        EXTRACT(QUARTER FROM INTERVAL) always returns 1
Details:

Tested on PostgreSQL Versions:
PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6
[FreeBSD] 20060305
PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)

SQL statemet
SELECT EXTRACT(QUARTER FROM INTERVAL '1 day');

Returns: 1
Expected: 0
      Or: ERROR: interval units "quarter" not supported

Am Freitag, 21. September 2007 schrieb Eugene M. Hutorny:
> SQL statemet
> SELECT EXTRACT(QUARTER FROM INTERVAL '1 day');
>
> Returns: 1
> Expected: 0
>       Or: ERROR: interval units "quarter" not supported

An alternative behavior would be to return something like

 ceil($argument / (360/4))

or however one would calculate the quarter exactly.

The current behavior makes no sense to me either.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

"Eugene M. Hutorny" <eugene@ksf.kiev.ua> writes:
> SQL statemet
> SELECT EXTRACT(QUARTER FROM INTERVAL '1 day');

> Returns: 1
> Expected: 0

Why would you expect that?  Quarters are numbered 1 through 4.
And it doesn't "always return 1":

regression=# SELECT EXTRACT(QUARTER FROM INTERVAL '3 months');
 date_part
-----------
         2
(1 row)

regression=# SELECT EXTRACT(QUARTER FROM INTERVAL '11 months');
 date_part
-----------
         4
(1 row)


            regards, tom lane

I expect 0 because

SELECT EXTRACT(YEAR FROM INTERVAL '1 day'), EXTRACT(MONTH FROM INTERVAL '1
day')

returns 0,0

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Eugene M. Hutorny" <eugene@ksf.kiev.ua>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, September 21, 2007 7:21 PM
Subject: Re: [BUGS] BUG #3624: EXTRACT(QUARTER FROM INTERVAL) always returns
1


"Eugene M. Hutorny" <eugene@ksf.kiev.ua> writes:
> SQL statemet
> SELECT EXTRACT(QUARTER FROM INTERVAL '1 day');

> Returns: 1
> Expected: 0

Why would you expect that?  Quarters are numbered 1 through 4.
And it doesn't "always return 1":

regression=# SELECT EXTRACT(QUARTER FROM INTERVAL '3 months');
 date_part
-----------
         2
(1 row)

regression=# SELECT EXTRACT(QUARTER FROM INTERVAL '11 months');
 date_part
-----------
         4
(1 row)


regards, tom lane

Tom Lane wrote:
> "Eugene M. Hutorny" <eugene@ksf.kiev.ua> writes:
> > SQL statemet
> > SELECT EXTRACT(QUARTER FROM INTERVAL '1 day');
> >
> > Returns: 1
> > Expected: 0
>
> Why would you expect that?  Quarters are numbered 1 through 4.
> And it doesn't "always return 1":
>
> regression=# SELECT EXTRACT(QUARTER FROM INTERVAL '3 months');
>  date_part
> -----------
>          2
> (1 row)

SELECT EXTRACT(QUARTER FROM INTERVAL '200 days') gives 1.  Why is that?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> SELECT EXTRACT(QUARTER FROM INTERVAL '200 days') gives 1.  Why is that?

Because interval_part does

                case DTK_QUARTER:
                    result = (tm->tm_mon / 3) + 1;
                    break;

Not sure that changing this is a good idea --- note that most of the
other cases also have blinders on about which fields of the struct pg_tm
to look at, and you'd need to make not-very-defensible assumptions about
conversion rates to incorporate other fields.  Possibly the correct
answer is "you should apply justify_interval first, if that's the
behavior you want".

regression=# select justify_interval(INTERVAL '200 days');
 justify_interval
------------------
 6 mons 20 days
(1 row)

regression=# select extract(quarter from justify_interval(INTERVAL '200 days'));
 date_part
-----------
         3
(1 row)

            regards, tom lane