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.

  • @RolandoMySQLDBA: Postgres is a perfectly valid alternative name for PostgreSQL.
    – user1822
    Dec 4, 2014 at 19:43
  • @a_horse_with_no_name OK I can rollback Dec 4, 2014 at 19:45
  • @RolandoMySQLDBA: no need to rollback.
    – user1822
    Dec 4, 2014 at 19:46

3 Answers 3


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

Result: 28.5

Result: 982384720.12

See PG docs

  • Well, i did'nt think about seconds. I was thinking about month or year field. It can't be fractional. Does it?
    – simar
    Dec 4, 2014 at 15:18
  • @simar: Not sure to be honest. Never tried it.. but I think not
    – gbn
    Dec 5, 2014 at 7:26

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.

  • Why the downvote?
    – Nerian
    Mar 22, 2017 at 9:51
  • I'm curious about why the downvote as well. While this doesn't answer the question of "why", it seems like a reasonable response to the question. Aug 1, 2017 at 20:39
  • 1
    This rounds the value. If you need to truncate it, use SELECT TRUNC(EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:29.6'))
    – xonya
    Apr 23, 2018 at 12:09

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.