SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting data based on age (stored as timestamp)

    Hi there

    I have a bit of an issue which I'm unsure about how to overcome....

    In my database I have a table of people. One of the fields is date of birth, which I've stored as an integer (timestamp like that generated by the php time() function).

    What I need to be able to do is select some people based on their age. I have a simple php function to work out a persons age based on their dob timestamp, but obviously I can't use this within the query.

    Any ideas on the best way to filter results by a specific age?

    Many thanks

  2. #2
    SitePoint Zealot
    Join Date
    Jun 2007
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can either do:

    CEIL((UNIX_TIMESTAMP() - age_timestamp) / 31556926) AS age

    Or use a different storage format which YEAR can be extracted from natively.

    The latter is the best optimised for performance.

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Speeple, thanks for your reply


    I can change the storage format if necessary. I've got into the habit of using INT as the datatype after it was recommended in a book I read a couple of years ago So, if I used a different datatype then how would I calculate the age as I can't just use the year they were born.

    Many thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    speeple, going by the year alone is not a good age calculation -- it is just an approximation

    an accurate age algorithm needs to check year, month, and day

    your age changes on your birthday, not a day sooner
    Code:
    select year(current_date) 
           - year(from_unixtime(dob))
           - case when month(current_date)
                     > month(from_unixtime(dob))      
                  then 0
                  when month(current_date)
                     < month(from_unixtime(dob))      
                  then 1
                  when dayofmonth(current_date)
                     < dayofmonth(from_unixtime(dob)) 
                  then 1
                  else 0 end    as age
      from users
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I'm a bit lost now

    Here is my current query:
    Code:
    SELECT
    	a.date,
    	a.performance,
    	e.measurement,
    	ath.firstname,
    	ath.lastname,
    	ath.gender
    FROM
    	activities a,
    	events e,
    	athletes ath
    WHERE
    	a.event_id = $eventid
    AND
    	e.event_id = $eventid
    AND
    	ath.athlete_id = a.athlete_id
    	AND FLOOR((UNIX_TIMESTAMP() - ath.dob) / 31556926) = $age
    ORDER BY
    	a.performance $sort
    LIMIT 100
    It appears to work but could probably be written better. I'm not sure how to incorporate your code into that though, Rudy.

    Many thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select * from (
    SELECT
     a.date,
     a.performance,
     e.measurement,
     ath.firstname,
     ath.lastname,
     ath.gender
         , year(current_date) 
           - year(from_unixtime(ath.dob))
           - case when month(current_date)
                     > month(from_unixtime(ath.dob))      
                  then 0
                  when month(current_date)
                     < month(from_unixtime(ath.dob))      
                  then 1
                  when dayofmonth(current_date)
                     < dayofmonth(from_unixtime(ath.dob)) 
                  then 1
                  else 0 end    as age
    FROM
     activities a,
     events e,
     athletes ath
    WHERE
     a.event_id = $eventid
    AND
     e.event_id = $eventid
    AND
     ath.athlete_id = a.athlete_id
    ) as d
    WHERE age = $age
    ORDER BY performance $sort LIMIT 100
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sxtrail View Post
    One of the fields is date of birth, which I've stored as an integer (timestamp like that generated by the php time() function).
    if you are using a database then why not use a date field in mysql and store yyyy-mm-dd then you don't need to do the time() calculation in php and you don't have to do the FROM_UNIXTIME calculation in mysql.

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    if you are using a database then why not use a date field in mysql and store yyyy-mm-dd then you don't need to do the time() calculation in php and you don't have to do the FROM_UNIXTIME calculation in mysql.
    I've absolutely no idea. It's just something I started doing after reading a well known PHP book and I've always stuck to it. How would you provide a date as a string to mysql to store it as a date? Also, is a date time field best for when storing things like when a news item was posted?

    Thanks Rudy for your solution, works great!

    Many thanks


Bookmarks

Posting Permissions

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