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: 0Why 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