如何计算 MySQL 中以月份为单位的两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5633821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to calculate difference between two dates in months in MySQL
提问by Awan
I have two columns in a MySQL table:
我在 MySQL 表中有两列：
- DateOfService (datetime)
- BirthDate (date)
- 服务日期（日期时间）
- 出生日期（日期）
I want to run a MySQL query that will provide date difference between these two fields in months.
我想运行一个 MySQL 查询，该查询将提供这两个字段之间的日期差异（以月为单位）。
How can I do this in a MySQL select query?
如何在 MySQL 选择查询中执行此操作？
Thanks.
谢谢。
采纳答案by Marco
This could work:
这可以工作：
SELECT 12 * (YEAR(DateOfService)
- YEAR(BirthDate))
+ (MONTH(DateOfService)
- MONTH(BirthDate)) AS months
FROM table
回答by Zane Bien
Have a look at the TIMESTAMPDIFF()function in MySQL.
看看MySQL中的TIMESTAMPDIFF()函数。
What this allows you to do is pass in two TIMESTAMPor DATETIMEvalues (or even DATEas MySQL will auto-convert) as well as the unit of time you want to base your difference on.
这允许您做的是传递两个TIMESTAMP或DATETIME值（甚至DATEMySQL 会自动转换）以及您希望基于差异的时间单位。
You can specify MONTHas the unit in the first parameter:
您可以MONTH在第一个参数中指定单位：
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')
-- 0
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')
-- 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')
-- 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')
-- 7
It basically gets the number of months elapsed from the first date in the parameter list. This solution accounts for the varying amount of days in each month (28,30,31) as well as leap years.
它基本上获取从参数列表中的第一个日期开始经过的月数。该解决方案考虑了每个月 (28,30,31) 和闰年的不同天数。
If you want decimal precision in the number of months elapsed, it's a little more complicated, but here is how you can do it:
如果您想要经过的月数的十进制精度，它会稍微复杂一些，但您可以这样做：
SELECT
TIMESTAMPDIFF(MONTH, startdate, enddate) +
DATEDIFF(
enddate,
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate)
MONTH
) /
DATEDIFF(
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate) + 1
MONTH,
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate)
MONTH
)
Where startdateand enddateare your date parameters, whether it be from two date columns in a table or as input parameters from a script:
在哪里startdate和enddate在你的日期参数，无论是从一个表或从脚本输入参数的两个日期列：
Examples:
例子：
With startdate = '2012-05-05' AND enddate = '2012-05-27':
-- Outputs: 0.7097
With startdate = '2012-05-05' AND enddate = '2012-06-13':
-- Outputs: 1.2667
With startdate = '2012-02-27' AND enddate = '2012-06-02':
-- Outputs: 3.1935
回答by Nikolay Yordanov
Try this:
尝试这个：
SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ...
SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ...
回答by IanS
The 'right' answer depends on exactly what you need. I like to round to the closest whole number.
“正确”的答案完全取决于您的需要。我喜欢四舍五入到最接近的整数。
Consider these examples: 1st January -> 31st January: It's 0 whole months, and almost 1 month long. 1st January -> 1st February? It's 1 whole month, and exactly 1 month long.
考虑以下示例： 1 月 1 日 -> 1 月 31 日：整整 0 个月，几乎有 1 个月之久。1 月 1 日 - > 2 月 1 日？整整1个月，正好1个月。
To get the number of wholemonths, use:
要获取整月数，请使用：
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-01-31'); => 0
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-02-01'); => 1
To get a roundedduration in months, you could use:
要获得以月为单位的舍入持续时间，您可以使用：
SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1
SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1
This is accurate to +/- 5 days and for ranges over 1000 years. Zane's answer is obviously more accurate, but it's too verbose for my liking.
这精确到 +/- 5 天，范围超过 1000 年。赞恩的回答显然更准确，但我不喜欢它太冗长了。
回答by M K
TRY with
试试看
PERIOD_DIFF(P1,P2)
PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMMor YYYYMM. Note that the period arguments P1 and P2 are not date values.
返回期间 P1 和 P2 之间的月数。P1 和 P2 的格式应为YYMM或YYYYMM。请注意，期间参数 P1 和 P2 不是日期值。
mysql> SELECT PERIOD_DIFF(200802,200703); -> 11
mysql> SELECT PERIOD_DIFF(200802,200703); -> 11
回答by Tu?n Nguyên Minh Th?nh
TIMESTAMPDIFF(MONTH, Start_date, End_date)
Example:
例子：
SELECT TIMESTAMPDIFF(MONTH, BirthDate, DateOfService) AS Months FROM Table
回答by Hanif
Based on a summary of all answers and a Google search, I think there are four almost similar ways to write it:
基于所有答案的总结和谷歌搜索，我认为有四种几乎相似的写法：
1)
1)
TIMESTAMPDIFF(MONTH, Start_date, End_date) AS Period
E.g.
例如
TIMESTAMPDIFF(MONTH, MIN(r.rental_date), MAX(r.rental_date)) AS Period
2)
2)
PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months
Or
或者
PERIOD_DIFF(date_format(End_date(), '%Y%m'), date_format(Start_date, '%Y%m')) as months
E.g.
例如
PERIOD_DIFF(date_format(MAX(r.rental_date), '%Y%m'), date_format(MIN(r.rental_date), '%Y%m')) as months
3)
3)
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months
OR
或者
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM End_date()), EXTRACT(YEAR_MONTH FROM Start_date)) AS months
E.g.
例如
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM MAX(r.rental_date)), EXTRACT(YEAR_MONTH FROM MIN(r.rental_date))) as Months
4)
4)
PERIOD_DIFF(concat(year(d1),if(month(d1)<10,'0',''),month(d1)), concat(year(d2),if(month(d2)<10,'0',''),month(d2))) as Months**
E.g.
例如
PERIOD_DIFF(
concat(year(MAX(r.rental_date)),if(month(MAX(r.rental_date))<10,'0',''),month(MAX(r.rental_date))),
concat(year(MIN(r.rental_date)),if(month(MIN(r.rental_date))<10,'0',''),month(MIN(r.rental_date)))
) as Months
