MySQL - Getting age and numbers of days between two dates

Question

I am trying to query a huge database (aprroximately 20 millions records) to get some data. This is the query I am working on right now.

SELECT a.user_id, b.last_name, b.first_name, c.birth_date FROM users a
INNER JOIN users_signup b ON a.user_id a = b.user_id
INNER JOIN users_personal c ON a.user_id a = c.user_id
INNER JOIN
(
SELECT distinct d.a.user_id FROM users_signup d
WHERE d.join_date >= '2013-01-01' and d.join_date < '2014-01-01'
) 
AS t ON a.user_id = t.user_id

I have some problems trying to retrieve additional data from the database. I would like to add 2 additional field to the results table:

1. I am able to get the birth date but I would like to get the age of the members in the results table. The data is stored as 'yyyy-mm-dd' in the users_personal table.
2. I would like to get the total days since a member joined till the day the left (if any) from a table called user_signup using data from join_date & left_date (format: yyyy-mm-dd).

Answer 1

Or you can do just this ...

SELECT
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age_in_years,
    TIMESTAMPDIFF(MONTH, birthday, CURDATE()) AS age_in_month,
    TIMESTAMPDIFF(DAY, birthday, CURDATE()) AS age_in_days,
    TIMESTAMPDIFF(MINUTE, birthday, NOW()) AS age_in_minutes,
    TIMESTAMPDIFF(SECOND, birthday, NOW()) AS age_in_seconds
FROM
    table_name

Answer 2

Try this:

SELECT a.user_id, b.last_name, b.first_name, c.birth_date, 
       FLOOR(DATEDIFF(CURRENT_DATE(), c.birth_date) / 365) age, 
       DATEDIFF(b.left_date, b.join_date) workDays
FROM users a
INNER JOIN users_signup b ON a.user_id a = b.user_id
INNER JOIN users_personal c ON a.user_id a = c.user_id
WHERE b.join_date >= '2013-01-01' AND b.join_date < '2014-01-01'
GROUP BY a.user_id