PostgreSQL Extract() DOW : why not Date datatype in addition to timestamp?

11,905

The original question referenced version 8.1, the 9.5 documentation states:

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. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names:

And then later, specifically under dow:

dow

The day of the week as Sunday (0) to Saturday (6)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5

Note that extract's day of the week numbering differs from that of the to_char(..., 'D') function.

Share:
11,905
Tim
Author by

Tim

Updated on June 23, 2022

Comments

  • Tim
    Tim almost 2 years

    According to the PostgreSQL version 8.1 date-time function docs:

    dow The day of the week (0 - 6; Sunday is 0) (for timestamp values only)

    SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 5
    

    Why is not a Date datatype also a valid argument for this function? If the sequence of the days of the week does not change by locale:

      0 - 6; Sunday is 0 
    

    why would the time-component of a combined date-type value be needed to determine the ordinal of the day in the week? Wouldn't the date-chunk alone be sufficient?