Clever Geek Handbook

PgSQL will return a day ago

I am trying to figure out how to turn a daytime year ago into a date in PgSQL. When i do this

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

I am getting a timestamp:

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

So how does it go 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: Date Stamp Unrecognized Time Units"? Also, why, how can I do what I want to get only the date portion of the result of the original operation?

0


source to share


1 answer


Using

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

or



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

The standard PostgreSQL SQL function "extract ()" will work with timestamps, but a) "date" is not a valid argument to extract () and b) it returns subfields, not a set of subfields. Conceptually, a date is made up 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
+3


source






More articles:


All Articles