MySQL -两个日期之间的年龄和天数

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.

我正在查询一个庞大的数据库(大约2000万条记录)来获取一些数据。这是我正在处理的查询。

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. 我可以得到出生日期，但是我想要得到结果表中成员的年龄。数据以“yyyy-mm-dd”的形式存储在users_personal表中。
3. 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).
4. 我希望从一个名为user_signup的表(如果有的话)中获取自一个成员加入后的总天数(如果有的话)，并使用join_date和left_date(格式:yyyy-mm-dd)的数据。

4 个解决方案

#1

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

#2

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

#3

You can use datediff function to find number of days between two days like

您可以使用datediff函数来查找两天之间的天数

select datediff(date1,date2) from table


select datediff(curdate(),date2) from table

#4

Getting the current age in years:

以年计算当前年龄:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(DATE(NOW()), birthday)), '%Y') * 1 AS age FROM table_name;

How this works:

这是如何工作的:

1. datediff(date1, date2) gives the difference between two dates in days. Note that the date format of 'birthday' here is date: YYYY-MM-DD.
2. datediff(date1, date2)以天为单位给出两个日期之间的差异。注意这里的生日格式是日期:YYYY-MM-DD。
3. from_days converts days into a date format
4. from_days将天数转换为日期格式
5. date_format function extracts with '%Y' only the four digit year. Don't use '%y', because you only get a two digit year and some people are older then 99 years.
6. date_format函数只使用“%Y”提取四位数字的年份。不要用“%y”，因为你只有两位数的年份，有些人比你大99岁。
7. multiply the string with 1. This is a 'hack'. MySQL will convert a string like 'YYYY' into an integer.
8. 将字符串乘以1。这是一个“黑客”。MySQL将把“yyyyy”这样的字符串转换成整数。

Getting the current age in month (unlikley, but someone may need this)

按月计算当前年龄(不可能，但有人可能需要这个)

SELECT (DATE_FORMAT(FROM_DAYS(DATEDIFF(DATE(NOW()), birthday)), '%Y') * 1 * 12)
+ (DATE_FORMAT(FROM_DAYS(DATEDIFF(DATE(NOW()), birthday)), '%m') * 1) AS age_in_months
FROM table_name;

How this works:

这是如何工作的:

1. Mostly the same as age in years above.
2. 大部分都和年龄相同。
3. The years get muliplied by 12. A (earth) year has 12 months.
4. 到12岁时，年复一年。一年有12个月。
5. In the next step the months are extracted the same way as the years, but instead the flag '%Y' must be changed to '%m'.
6. 在下一步中，以与年份相同的方式提取月份，但是必须将标记“%Y”改为“%m”。
7. At the end the two values are added together.
8. 最后两个值相加。

Getting the current age in days is as simple as this:

以天为单位计算当前的年龄很简单:

SELECT DATEDIFF(DATE(NOW()), birthday) AS age_in_days FROM table_name;

Alternative code:

替代代码:

SELECT
    DATE_FORMAT(age_date, '%Y') * 1 AS age_in_years,
    (DATE_FORMAT(age_date, '%Y') * 1 * 12) + (DATE_FORMAT(age_date, '%m') * 1) AS age_in_months,
    age_in_days
FROM
    (SELECT 
        FROM_DAYS(DATEDIFF(DATE(NOW()), birthday)) AS age_date,
        DATEDIFF(DATE(NOW()), birthday) AS age_in_days
    FROM table_name) AS age_date;