Postgres Pro
Downloads
Home   >   mailing lists

Thread: Age function

Age function

From
"Andrus"
Date:
How to create function which returns persons age in years?

Function parameters:

ldDob - Day Of birth
ldDate - Day where age is returned


I tried

CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS
$_$
SELECT floor(INT($2::text::integer-$1::text::integer)/10000);
$_$ language sql


but got

ERROR: syntax error at or near "("


In VFP I can use

RETURN floor(INT((VAL(DTOS(ldDate))-VAL(DTOS(ldDob))))/10000)

or

RETURN  (year(ldDate) - year(ldDOB) - ;
 iif( str(month(ldDate),2) + str(day(tdDate),2) < ;
 str(month(tdDOB),2) + str(day(tdDOB),2), 1, 0) )


Andrus.

Re: Age function

From
"Alexander Staubo"
Date:
On 5/14/07, Andrus <kobruleht2@hot.ee> wrote:
> How to create function which returns persons age in years?
[snip]

What's wrong with age()?

# select age('1879-03-14'::date);
       age
------------------
 128 years 2 mons
# select extract(year from age('1879-03-14'::date));
 date_part
-----------
       128

You can give age() two arguments to calculate the difference between
two dates to get an interval:

# select age('1955-04-18'::date, '1879-03-14'::date);
          age
-----------------------
 76 years 1 mon 4 days

If you subtract two date values you will get the number of days
instead of an interval.

Documentation:

  http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

Alexander.

Re: Age function

From
Jon Sime
Date:
Andrus wrote:
> How to create function which returns persons age in years?
>
> Function parameters:
>
> ldDob - Day Of birth
> ldDate - Day where age is returned
>
>
> I tried
> CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS
> $_$
> SELECT floor(INT($2::text::integer-$1::text::integer)/10000);
> $_$ language sql

There's already an age(timestamp [, timestamp]) function available for this:

     select age('1912-06-23'::date);
or
     select age(now()::date, '1912-06-23'::date);

And if you want just the number of years, use date_part to extract just
that piece:

     select date_part('year', age(now()::date, '1912-06-23'::date));

Based on this and your other question about functions that followed, you
may want to read the Date and Time Functions section of the docs:

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

Re: Age function

From
Rich Shepard
Date:
On Mon, 14 May 2007, Andrus wrote:

> How to create function which returns persons age in years?

   Look at the PostgreSQL docs for "Date/Time Functions and Operators."
You'll find the syntax for AGE() there.

Rich

--
Richard B. Shepard, Ph.D.               |    The Environmental Permitting
Applied Ecosystem Services, Inc.        |          Accelerator(TM)
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863