Обсуждение: Auto Adjust Age
I've only been using PostgreSQL since Oct 2010 and it's my first experience with SQL or any ORDBMS. I've searched on the web and been creating my own database users, databases, tables from scratch which has been interesting to say the least but now I would like to know if this is possible in SQL or PostgreSQL since I can't find anything online that shows me how to do so. I've created a table called 'users' and I have it configured as follows: 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. Thank you so much.
Carlos Mennens <carlos.mennens@gmail.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).
If you must store the age then you have to run a daily batch to make the
bulk update.
HTH
--
Jerry Sievers
e: gsievers19@comcast.net
p: 305.321.1144
On 04/06/2011 08:53 AM, Carlos Mennens wrote: > I've only been using PostgreSQL since Oct 2010 and it's my first > experience with SQL or any ORDBMS. I've searched on the web and been > creating my own database users, databases, tables from scratch which > has been interesting to say the least but now I would like to know if > this is possible in SQL or PostgreSQL since I can't find anything > online that shows me how to do so. I've created a table called 'users' > and I have it configured as follows: > > 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. > > Thank you so much. > Change "age" to birthyear and do date arithmetic in your reporting. (Better yet get, birthdate to start with.)
On 04/06/2011 08:53 AM, Carlos Mennens wrote: > I've only been using PostgreSQL since Oct 2010 and it's my first > experience with SQL or any ORDBMS. I've searched on the web and been > creating my own database users, databases, tables from scratch which > has been interesting to say the least but now I would like to know if > this is possible in SQL or PostgreSQL since I can't find anything > online that shows me how to do so. I've created a table called 'users' > and I have it configured as follows: > > 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. > > Thank you so much. > oops you do have dob. Drop age and do the date manipulation "at runtime"
On Wed, Apr 06, 2011 at 10:53:49AM -0400, Carlos Mennens wrote:
> online that shows me how to do so. I've created a table called 'users'
> and I have it configured as follows:
>
> 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'?
Why do you have the age stored at all? When you SELECT from the table
and want someone's age, just do
SELECT [. . .], extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
. . . FROM users . . .
By and large, it's not a good idea to store something you can
calculate from other data you have.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
On Wed, Apr 6, 2011 at 11:15 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
> Carlos Mennens <carlos.mennens@gmail.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).
>
> If you must store the age then you have to run a daily batch to make the
> bulk update.
Theres also the age() function....
SELECT age(dob);
Should give you
age
-------------------------
31 years 5 mons 17 days
If you want to be really exact about it. :)
JC de Villa
On Wed, Apr 06, 2011 at 11:15:31AM -0400, Jerry Sievers wrote:
> I'd suggest not storing age but instead wrapping with a view that calls
> date_trunc('year', now() - dob).
Or put that in the query:
SELECT
...,
date_trunc('year', now() - dob) as age
FROM
users
;
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Wed, Apr 6, 2011 at 11:20 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> Why do you have the age stored at all? When you SELECT from the table
> and want someone's age, just do
>
> SELECT [. . .], extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
> . . . FROM users . . .
>
> By and large, it's not a good idea to store something you can
> calculate from other data you have.
I'm very sorry as I didn't know PG or SQL could auto calculate age
with existing parameters. I'm trying to learn as much SQL as I can
during my spare time. I tried the following and it worked great!
ide=# SELECT extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
FROM users;
age
-----
31
(1 row)
THANK YOU;
On Wed, Apr 6, 2011 at 11:24 AM, JC de Villa <jc.devilla@gmail.com> wrote:
> Theres also the age() function....
>
> SELECT age(dob);
>
> Should give you
>
> age
> -------------------------
> 31 years 5 mons 17 days
>
> If you want to be really exact about it. :)
That worked awesome too!
ide=# SELECT age(dob) FROM users;
age
--------------------------
31 years 10 mons 12 days
(1 row)
On 2011-04-06, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> I've only been using PostgreSQL since Oct 2010 and it's my first
> experience with SQL or any ORDBMS. I've searched on the web and been
> creating my own database users, databases, tables from scratch which
> has been interesting to say the least but now I would like to know if
> this is possible in SQL or PostgreSQL since I can't find anything
> online that shows me how to do so. I've created a table called 'users'
> and I have it configured as follows:
>
> 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'm not sure how id can be used here.
Running this each morning will keep it current. for the normal
definition of human age.
update user set age=extract('year' from age(dob))
where age is distinct from extract('year' from age(dob));
a cleaner way is to create a view and present a computed age
column in the view
--
⚂⚃ 100% natural
On 2011-04-06, Jerry Sievers <gsievers19@comcast.net> wrote:
> Carlos Mennens <carlos.mennens@gmail.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.
--
⚂⚃ 100% natural
In response to Jasen Betts <jasen@xnet.co.nz>:
> On 2011-04-06, Jerry Sievers <gsievers19@comcast.net> wrote:
> > Carlos Mennens <carlos.mennens@gmail.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/