Auto Ahidjust Age

CarlosinFL · #1 (**permalink**) 04-05-11, 21:58

I don't know if this is a stupid question but I've been asked to create a contact list and there's a column requirement for the contacts dob DATE & age INT. My question is how would I be able to get the value for 'age' to adjust as time goes by based on their 'dob'? I've never done anything like this before using SQL statements.

Thanks for any help!

r937 · #2 (**permalink**) 04-06-11, 06:53

if the table contains only DOB, could you not define a view which calculates age on the fly?

if that doesn't satisfy whoever it is that "asked" you to have an age column, ask them to look up PHB in wikipedia

CarlosinFL · #3 (**permalink**) 04-06-11, 09:06

Quote:

Originally Posted by r937

if the table contains only DOB, could you not define a view which calculates age on the fly?

That was specifically my question. I looked online and searched here to see if anyone has ever "defined" a view which calculates age on the fly in the 'age' column based on the 'dob' column but I couldn't find any examples of how to formulate this in SQL. I'm assuming based on your response, that it can be. My question is how?

Quote:

Originally Posted by r937

]if that doesn't satisfy whoever it is that "asked" you to have an age column, ask them to look up PHB in wikipedia

I checked PHB in Wikipedia and unless we're referencing a Dilbert character or college degree, I have no idea what you're referencing here.

PHB - Wikipedia, the free encyclopedia

r937 · #4 (**permalink**) 04-06-11, 09:53

Quote:

Originally Posted by CarlosinFL

My question is how?

Code:

CREATE VIEW myview ( id, dob, age, ... )
AS
SELECT id, dob, YEAR(CURRENT_DATE)-YEAR(dob) AS age, ...
  FROM daTable

subtracting the years gives a close approximation of age, you can also use a more exact calculation

yes, PHB refers to the micro-managing idiot boss in Dilbert

futurity · #5 (**permalink**) 04-06-11, 10:41

Why not just calculate it dynamically during your select?

Code:

select
    ...
    date_part('years', age(current_date, dob)) as age,
    ...
from some_table
where ....;

CarlosinFL · #6 (**permalink**) 04-06-11, 10:49

Quote:

Originally Posted by futurity

Why not just calculate it dynamically during your select?

Code:

select
    ...
    date_part('years', age(current_date, dob)) as age,
    ...
from some_table
where ....;

I don't know but since 'age' is a column entry, when that SELECT statement is ran as:

Code:

SELECT * FROM users ORDER BY id;

wont the age parameters be wrong or invalid unless I run your suggested code?

r937 · #7 (**permalink**) 04-06-11, 11:08

Quote:

Originally Posted by CarlosinFL

... since 'age' is a column entry

we are suggesting that you ~not~ have an age column

whoa, wait a sec, postgresql has an AGE() function?

that's awesome! who knew?!!

CarlosinFL · #8 (**permalink**) 04-06-11, 11:11

Sorry - it wasn't clear to me that you suggest I omit the 'age' column in place if the AGE() function. It may seem elementary to you guys but I've only been using PG since 10/10 and am slowly learning everything by reading and self implementation. Thanks for the suggestion!

Code:

ALTER TABLE users DROP COLUMN age;

CarlosinFL · #9 (**permalink**) 04-06-11, 11:24

Quote:

Originally Posted by futurity

Why not just calculate it dynamically during your select?

Code:

select
    ...
    date_part('years', age(current_date, dob)) as age,
    ...
from some_table
where ....;

I guess I don't quite follow this code based on my table parameters:

Code:

ide=# \d users
            Table "public.users"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 fname  | character varying(40) | not null
 lname  | character varying(40) | not null
 email  | character varying     | not null
 office | integer               | not null
 dob    | date                  | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Code:

ide=# SELECT * FROM users;
 id | fname  |  lname  |        email        | office |    dob     
----+--------+---------+---------------------+--------+------------
  1 | Carlos | Mennens | carlos@iamghost.org |    124 | 1980-05-12
(1 row)

When you suggest the code above, I'm having a hard time understanding the relation to my table.

I tried to relate the example but I'm missing something:

Code:

ide=# SELECT dob('years',age(current_date, dob)) as age WHERE id = 1;
ERROR:  column "dob" does not exist
LINE 1: SELECT dob('years', age(current_date, dob)) as age WHERE id =...
                                               ^

r937 · #10 (**permalink**) 04-06-11, 11:31

Quote:

Originally Posted by CarlosinFL

... but I'm missing something

yup, you're missing the FROM clause

CarlosinFL · #11 (**permalink**) 04-06-11, 11:34

Code:

ide=# SELECT extract('years' from age(CURRENT_TIMESTAMP,dob)) as age FROM users; 
 age 
-----
  31
(1 row)

ide=# SELECT age(dob) FROM users;
           age            
--------------------------
 31 years 10 mons 12 days
(1 row)

Thanks. Got too excited and jumped the gun!

Internet Services

Web Development

Digital Marketing

Consumer Tech