如何使用MySQL查询获取两个日期之间的月份差异？

Question

如何使用MySQL查询获取两个日期之间的月份差异？

mysqldatedatediffdate-arithmetic

115

我想要计算两个日期时间字段之间的月数。

MySQL 中是否有更好的方法，而不是获取 Unix 时间戳，然后除以 2592000（秒），并向上取整？

- Darryl Hein

21个回答

109

PERIOD_DIFF 函数计算两个日期之间相差的月份数。

例如，要计算当前时间与你的表中某个时间列之间的差异：

select period_diff(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months from your_table;

- Max Caceres

1

你知道当它是11个月零1天时会发生什么吗？这样就是12个月，但如果你改成30天的月份，那么它仍然是11个月。 - Darryl Hein

1

11个月零1天会被转换成11个月。PERIOD_DIFF无法区分30天还是31天的月份。 - Max Caceres

35

我同时使用PERIOD_DIFF函数。为了获取日期的年份和月份，我使用EXTRACT函数：

  SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;

- Smolla

2

太棒了！它使用的时间比DATE_FORMAT方法少50％，谢谢！+1 - David Rodrigues

1

针对Unix时间戳，您可以使用以下SQL查询语句：

SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(time, "%Y%m%d"))) AS months FROM your_table;

- Smolla

3

这是不正确的，PERIOD_DIFF 不接受天数值，因此您正在计算最接近整月的月份数，如果不足一个整月。您应该编辑您的答案以澄清这一点。 - rgvcorley

是的，为了详细说明使用此方法的警告：它给出时间段跨越当前月份的日历月数。例如，如果时间差为5天但两个日期在同一个月份，结果将为0个月。如果时间差为5天，但跨越从九月到十月，那么结果将为1个月。 - BadHorsie

非常适合我！只计算月份的转数，这正是我所需要的。 - Rawburner

20

DATEDIFF 函数可以计算出两个日期之间的天数。这样更加准确，因为...如何定义一个月呢？（28、29、30或31天？）

- SoapBox

73

PERIODDIFF 可以精确计算月份数量。 - Max Caceres

11

“更准确”在日期方面是非常主观的。例如，如果您从事需要按月计算的业务，知道两个日期之间有多少个月份可能比知道天数更重要，正如您所述的那样。一个月有多长时间？如果我需要计算月份数，我不能简单地除以30。 - Thomas Paine

6

这不是正确的答案；你应该将下面 Max 的回答标记为正确。 - Graham Charles

2

为什么PERIODDIFF评论会有那么多赞？Perioddiff不接受天数值，所以如果不足整月，你正在计算最接近的整月数量并向上取整。 - rgvcorley

2

哦，我是多么喜欢这种回答啊。它不仅回答了问题，还试图向某人解释世界的运作方式。18岁以上并标记为答案。我不明白这里有些人在想什么... - Peter VARGA

显示剩余4条评论

16

正如这里的许多答案所展示的那样，'正确'的答案取决于你需要什么。在我的情况下，我需要将其四舍五入为最接近的整数。

考虑以下例子： 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

这是精确到+/- 5天，适用于超过1000年的范围。Zane的答案显然更准确，但对我来说太冗长了。

- IanS

我不同意... SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-02-04'); 会得到0个月的差距... SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-03-01'); 会得到1... 如果5天是0，那么29天就是1？ - Scott

@Scott，一月和三月之间有一个完整的月份（称为二月）。TIMESTAMPDIFF只会给出日期之间完整月份的数量。如果您想让任何一个月份大小的时间段等于一个月，请使用我提供的四舍五入版本。 - IanS

9

我更喜欢这种方式，因为每个人一眼就能清晰地理解它：

SELECT
    12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
    tab;

- Stanislav Basovník

Stanislav - 如果你想按月份了解数据，这种方法是完全正确的。我有一些报告显示每个月的销售情况（如1月/2月/3月等），但是由于有太多的数据（按季度/年份/过去几年的图表等），我无法进行任何分组。我需要提取原始数据并循环遍历它-当日期为1/31时，许多计算会将“下个月”放入三月，而我们人类知道它是二月。如果日期是2/4，则某些计算会说1/31是“本月”，但1/28是“上个月”。 - Scott

8

根据MySQL手册：

PERIOD_DIFF(P1,P2)

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

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

因此，可能可以像这样做：

Select period_diff(concat(year(d1),if(month(d1)<10,'0',''),month(d1)), concat(year(d2),if(month(d2)<10,'0',''),month(d2))) as months from your_table;

d1和d2是日期表达式。

我不得不使用if()语句来确保月份是两位数，例如02而不是2。

- Vincent Ramdhanie

6

有更好的方法吗？有。不要使用MySQL时间戳。除了占用36个字节外，它们并不方便使用。我建议对所有日期/时间值使用朱利安日期和从午夜开始的秒数。这些可以组合成UnixDateTime。如果将其存储在DWORD（无符号4字节整数）中，则可以将日期存储为自1970年1月1日时代以来的秒数，直到2106年。 DWORD最大值= 4,294,967,295 - DWORD可以容纳136年的秒数

朱利安日期在进行日期计算时非常好用 UNIXDateTime值在进行日期/时间计算时很好用两者都不好看，所以当我需要一个列而不会进行太多计算但我想要一个一目了然的指示时，我使用时间戳。

在良好的语言中，转换为朱利安日期和返回可以非常快地完成。使用指针，我将其缩短到约900个时钟周期（当然，这也是从字符串到整数的转换）

当你进入像金融市场这样使用日期/时间信息的严肃应用程序时，朱利安日期就是事实标准。

- Mike Trader

你从哪里得到了“占用36个字节”的信息？MySQL手册指出TIMESTAMP列的存储需求为4个字节。http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html - poncha

现在就开始计划Y2106的Bug吧！;-) - ErichBSchulz

5

查询将会是这样的：

select period_diff(date_format(now(),"%Y%m"),date_format(created,"%Y%m")) from customers where..

提供了一个日历月数，自客户记录创建日期时间戳以来，让MySQL在内部进行月份选择。

- Mike Harrison

2

DROP FUNCTION IF EXISTS `calcula_edad` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `calcula_edad`(pFecha1 date, pFecha2 date, pTipo char(1)) RETURNS int(11)
Begin

  Declare vMeses int;
  Declare vEdad int;

  Set vMeses = period_diff( date_format( pFecha1, '%Y%m' ), date_format( pFecha2, '%Y%m' ) ) ;

  /* Si el dia de la fecha1 es menor al dia de fecha2, restar 1 mes */
  if day(pFecha1) < day(pFecha2) then
    Set vMeses = VMeses - 1;
  end if;

  if pTipo='A' then
    Set vEdad = vMeses div 12 ;
  else
    Set vEdad = vMeses ;
  end if ;
  Return vEdad;
End

select calcula_edad(curdate(),born_date,'M') --  for number of months between 2 dates

- Rama

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

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

- Zane Bien · Accepted Answer

计算任意两个日期之间的月份差异：

我很惊讶这还没有被提到：

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

该函数允许您传入两个 TIMESTAMP 或 DATETIME 值（甚至可以是 DATE，因为 MySQL 会自动转换），以及您要基于的时间单位。

您可以在第一个参数中指定 MONTH 作为单位：

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')
-- Outputs: 0

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')
-- Outputs: 1

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')
-- Outputs: 1

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')
-- Outputs: 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