Re: Auto Adjust Age

In response to Jasen Betts <jasen(at)xnet(dot)co(dot)nz>:

> On 2011-04-06, Jerry Sievers <gsievers19(at)comcast(dot)net> wrote:
> > Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com> writes:
> >
> >> CREATE TABLE users
> >> (
> >> id integer PRIMARY KEY UNIQUE NOT NULL, --ID
> >> fname character varying(40) NOT NULL, --First name
> >> lname character varying(40) NOT NULL, --Last name
> >> email character varying NOT NULL, --email address
> >> office integer NOT NULL, --Office number
> >> dob date NOT NULL, --Date of birth
> >> age integer NOT NULL --Age
> >> )
> >> ;
> >>
> >> Is there a way in SQL I can have the users 'age' be auto adjusted
> >> based on the 'id' & 'dob'? I would assume this is possible because if
> >> you have 100 employees, I doubt someone has time to sit and change
> >> everyone's age from 31 > 32 on their birthday. Can someone please help
> >> explain how this works or what the SQL code would look like assuming
> >> that it's possible? I have no advanced far enough to see what triggers
> >> and views are so perhaps it's just my level with SQL in general.
> >
> > I'd suggest not storing age but instead wrapping with a view that calls
> > date_trunc('year', now() - dob).
>
> unfortunately that doesn't work.
>
> now() - dob gives you a number of days, and there's no reliable way to
> convert a number of days into a number of years.
>
> someone born 365 days ago today is 1 year old.
> but in a years time someone then 365 days old would not be because of
> the leap year.

use age() instead. I assume that's what it's designed for:

SELECT age('4/29/1972', now());

Aside from that minor tweak, _DEFINITELY_ get rid of the age column and
make the view.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/