PgSQL turning day-of-year back into date

Question

I am trying to determine how to turn a day-of-year back into a date in PgSQL. When I do this

select date '2013-01-01' + interval '53 days'

I get a timestamp:

"2013-02-23 00:00:00"

So how come when I do any of the following

select extract(date from (date '2013-01-01' + interval '53 days'))

select extract(date from (select date '2013-01-01' + interval '53 days'))

I get "ERROR: timestamp units "date" not recognized"? Besides the why, how can I do what I want, which is to only get the date portion of the result of the original operation?

Answer 1

Use

select (date '2013-01-01' + interval '53 days')::date

or

select cast(date '2013-01-01' + interval '53 days' as date)

PostgreSQL's standard SQL function "extract()" will operate on timestamps, but a) "date" isn't a valid argument to extract(), and b) it returns subfields, not a collection of subfields. Conceptually, a date consists of a collection of three subfields: year, month, and day.

select extract(year from current_timestamp),
       extract(month from current_timestamp),
       extract(day from current_timestamp),
       -- Concatenate and cast to type "date".
       (extract(year from current_timestamp) || '-' || 
       extract(month from current_timestamp) || '-' ||
       extract(day from current_timestamp))::date