Function extract() from date returns double precision value

Question

Why does the Postgres function extract() return double precision from date value?

Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported by Postgres), calculate date/time differently and possible return float number.

Answer 1

Because the return values can be large or a decimal number.
Double precision will accept a wide range of values

Looking at other numeric types, you only have decimal which will have overhead: you don't know the return scale or precision needed beforehand so it would have to wide

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12

See PG docs

Answer 2

You can always cast the result of extract.

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40')::integer;

That way you always get an integer.

Answer 3

Even if extracting fields from a date would always produce results that could fit in an integer, according to the doc, extract doesn't directly work on a date type:

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. (Expressions of type date are cast to timestamp and can therefore be used as well.)

So extract(date...) does not exist as such, rather it's extract(timestamp...) with an implicit cast of the argument, and extract(timestamp...) can't return only integers, for instance extract(seconds from timestamp) has a fractional part.

In addition to that, extract(epoch from date) doesn't fit in a postgres integer for dates beyond the 19 January 2038 (see The Year 2038 problem), which will be become a real problem as we progress towards that date.