2

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?

Marvo
  • 17,845
  • 8
  • 50
  • 74
Steve
  • 945
  • 3
  • 13
  • 22
  • http://stackoverflow.com/questions/6133107/extract-date-yyyy-mm-dd-from-a-timestamp-in-postgresql – Tim Apr 19 '13 at 20:59

1 Answers1

5

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
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185