如何计算MySQL中两个月的日期差异?

I have two columns in a MySQL table:

MySQL表中有两列:

• DateOfService (datetime)
• 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.

谢谢。

7 个解决方案

#1

This could work:

这可能工作:

SELECT 12 * (YEAR(DateOfService) 
              - YEAR(BirthDate)) 
       + (MONTH(DateOfService) 
           - MONTH(BirthDate)) AS months 
FROM table

#2

Have a look at the TIMESTAMPDIFF() function in MySQL.

查看一下MySQL中的TIMESTAMPDIFF()函数。

What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on.

这允许您通过两个时间戳或DATETIME值(甚至是MySQL自动转换的日期)，以及您想要根据的时间单位。

You can specify MONTH as the unit in the first parameter:

您可以在第一个参数中指定月份为单元:

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 startdate and enddate are 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

#3

Try this:

试试这个:

SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ...

选择DATEDIFF(DateOfService, BirthDate) / 30，从…

#4

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 YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

返回期间P1和P2之间的月数。P1和P2应该是YYMM或yyymm格式。注意，周期参数P1和P2不是日期值。

mysql> SELECT PERIOD_DIFF(200802,200703); -> 11

mysql >选择PERIOD_DIFF(200802、200703);- > 11

#5

TIMESTAMPDIFF(MONTH, Start_date, End_date)

Example:

例子:

SELECT TIMESTAMPDIFF(MONTH, BirthDate, DateOfService) AS Months FROM Table

#6

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日->月31日:整个月是0个月，几乎是1个月。1月1日- 2月1日?1个月，正好1个月。

To get the number of whole months, use:

为了得到整个月的数量，使用:

SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-01-31');  => 0
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-02-01');  => 1

To get a rounded duration 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年以上。赞恩的答案显然更准确，但我喜欢它太啰嗦。

#7

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