Search Postgresql Archives

Re: Auto Adjust Age

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



In response to Jasen Betts <jasen@xxxxxxxxxx>:

> On 2011-04-06, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:
> > Carlos Mennens <carlos.mennens@xxxxxxxxx> 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/

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux