Consider the following query:
SELECT
INTERVAL '1 month' * v AS i,
EXTRACT(YEAR FROM INTERVAL '1 month' * v) AS y,
EXTRACT(QUARTER FROM INTERVAL '1 month' * v) AS q,
EXTRACT(MONTH FROM INTERVAL '1 month' * v) AS m
FROM generate_series(0, 16) t(v)
It yields, to my surprise:
i |y|q|m |
-------------|-|-|--|
00:00:00|0|1| 0|
1 mon|0|1| 1|
2 mons|0|1| 2|
3 mons|0|2| 3|
4 mons|0|2| 4|
5 mons|0|2| 5|
6 mons|0|3| 6|
7 mons|0|3| 7|
8 mons|0|3| 8|
9 mons|0|4| 9|
10 mons|0|4|10|
11 mons|0|4|11|
1 year|1|1| 0|
1 year 1 mon|1|1| 1|
1 year 2 mons|1|1| 2|
1 year 3 mons|1|2| 3|
1 year 4 mons|1|2| 4|
So, when extracting a QUARTER from such a "normalized" INTERVAL (produced by an INTERVAL '1 month' * <some integer> expression), we get values 1-4 (as if this extraction were somehow 1-based), whereas extracting a YEAR or MONTH from an INTERVAL produces values 0-N (YEAR) or 0-11 (MONTH), respectively.
What's the rationale behind this behaviour and is it documented? (I do not think it is documented here, explicitly)
Yes it is.
You seem to be going out of your way to avoid a plain reading of the documentation. Section 9.1.1 starts:
9.9.1. EXTRACT, date_part
EXTRACT(field FROM source)The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. ... The following are valid field names
(My emphasis)
It then goes on to describe each field and the values that are possible. Absent any indicators to the contrary, then, this is documentation for what happens when extracting fields from an interval.
And, indeed we see for example day:
For
timestampvalues, the day (of the month) field (1 - 31) ; forintervalvalues, the number of days
So, we can see that, where they wish to highlight a difference between interval and timestamp, they can do it in individual field descriptions. Similarly for month they separately document timestamp and interval handling, so they seem to have a consistent way of documenting differences.
So, finally, we get to quarter:
The quarter of the year (1 - 4) that the date is in
That's it. That is the documentation, and it applies equally to timestamps and intervals.