如何在MySQL中计算两个日期之间的月份差异

Question

如何在MySQL中计算两个日期之间的月份差异

mysqldate

11

我在MySQL表中有两个列：

DateOfService (datetime)
BirthDate (date)

我想运行一个MySQL查询，以便在月份中提供这两个字段之间的日期差。

如何在MySQL选择查询中执行此操作？

谢谢。

- Awan

你能举个例子说明你期望的输出是什么吗？例如：今天是4月12日，我的生日是4月14日或5月12日或5月15日。你期望从查询中获得哪些值？ - Teetrinker

请返回翻译文本。 - manji

服务日期列的日期将始终大于出生日期列。因此，我需要从服务日期中减去出生日期，并计算月份差异。 - Awan

7个回答

10

这个可能可行：

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

- Marco

我使用了这个查询..问题出现在特定日期。日期是2014-01-30到2014-03-02，只相差30天。但它返回2个月的差异。 - Vaishu

4

尝试这个： SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ... 这段代码涉及到IT技术。它的作用是计算出两个日期之间相差的月数。具体的实现方法是使用DATEDIFF函数获取两个日期之间的天数差，再除以30得到月数。

- Nikolay Yordanov

2

“正确”的答案取决于您的具体需求。我喜欢“四舍五入到最近的整数”。

考虑以下例子： 1月1日->1月31日：它是0个整月，几乎1个月长。 1月1日->2月1日？它是1个整月，正好1个月长。

要获取“整”月数，请使用：

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

为了获得以月为单位的“四舍五入”时间，您可以使用以下代码：

要获得以月为单位的“四舍五入”时间，您可以使用：

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

这是在1000年范围内，精度可以达到+/- 5天。Zane的回答显然更准确，但对我来说太冗长了。

- IanS

2

基于所有答案的总结和谷歌搜索，我认为有四种几乎相似的编写方式:

1)

TIMESTAMPDIFF(MONTH, Start_date, End_date) AS Period

例如。

TIMESTAMPDIFF(MONTH, MIN(r.rental_date), MAX(r.rental_date)) AS Period

2)

PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months

或者

PERIOD_DIFF(date_format(End_date(), '%Y%m'), date_format(Start_date, '%Y%m')) as months

例如：

PERIOD_DIFF(date_format(MAX(r.rental_date), '%Y%m'), date_format(MIN(r.rental_date), '%Y%m')) as months

3)

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months

或者

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM End_date()), EXTRACT(YEAR_MONTH FROM Start_date)) AS months

例如：

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM MAX(r.rental_date)), EXTRACT(YEAR_MONTH FROM MIN(r.rental_date))) as Months

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**

例如：

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

- Hanif

2

TIMESTAMPDIFF(MONTH, Start_date, End_date)

示例：

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

- Tuấn Nguyên Minh Thịnh

2

使用

PERIOD_DIFF(P1,P2)

返回期间P1和P2之间的月数。P1和P2应该是格式为YYMM或YYYYMM的字符串。请注意，期间参数P1和P2不是日期值。

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

- M K

这个回答没有解决提问者的问题，因为它不接受日期时间格式或日期格式。也许如果这个问题被编辑过，并且表明了这一点，我可能会撤销我的负1评价。 - amaster

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Zane Bien · Accepted Answer

请查看 MySQL 中的 TIMESTAMPDIFF() 函数。

该函数可以让您传递两个 TIMESTAMP 或 DATETIME 值（甚至可以是 DATE，因为 MySQL 会自动转换），以及基于哪个时间单位计算差值。

您可以在第一个参数中指定 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

这段代码基本上是获取参数列表中第一个日期至今的月份数。此解决方案考虑了每个月的天数（28、30、31），以及闰年的影响。

如果你想在计算月份时保留小数精度，那么就需要稍微复杂一些，但以下是如何实现：

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
  )

当你需要使用日期参数时，可以使用 startdate 和 enddate，无论是从表格的两个日期列中获取还是从脚本的输入参数中获取：

例如：

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