From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: extract or date_part on an interval?  How many e
Date: Sun, 27 Jan 2008 18:56:58 -0800
User-Agent: KMail/1.9.1
Cc: Bryce Nesbitt <bryce1@obviously.com>
References: <479D3E63.2080601@obviously.com>
In-Reply-To: <479D3E63.2080601@obviously.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200801271856.58853.aklaver@comcast.net>

On Sunday 27 January 2008 6:30 pm, Bryce Nesbitt wrote:
> Hmm.  Seemed so simple.  But how do I get the number of years an
> interval represents?  extract is clearly the wrong way:
>
> stage=# select 'now()-'1987-02-01' as interval,extract(year from
> now()-'1987-02-01') as age;
>         interval          | age
> --------------------------+-----
> 7665 days 18:05:51.660345 |   0
>
> select EXTRACT(year FROM INTERVAL '7665 days'); gives the same result.
>
> select EXTRACT(day FROM now()-'1987-02-01')/365 as age; seems messy.
> select (now()-'1987-02-01')/365; gives extraneous junk.
>
>  Thanks!

SELECT age('1987-02-01'::timestamp);
           age
--------------------------
 20 years 11 mons 26 days
(1 row)

-- 
Adrian Klaver
aklaver@comcast.net