Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Auto Adjust Age

    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!
    Last edited by CacheDrive; 04-06-11 at 10:14.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    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 View Post
    ]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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    277
    Why not just calculate it dynamically during your select?

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

  6. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by futurity View Post
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    ... 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?!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    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;

  9. #9
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by futurity View Post
    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 =...
                                                   ^

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    ... but I'm missing something
    yup, you're missing the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •