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

Tim picture Tim · Jan 22, 2013 · Viewed 9.7k times · Source

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?

Answer

Snowman picture Snowman · Jun 1, 2016

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.