Is PostgreSQL's EXTRACT(QUARTER FROM <interval>) 1-based?

user_521799 0
Viewed 792

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)

intervals postgresql sql
1 Answers

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 timestamp values, the day (of the month) field (1 - 31) ; for interval values, 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.

user_154980
Related