MySQL中日期之间的差异

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

有没有比获得unix时间戳和除以2 592 000(秒)更好的方法并在MySQL中进行四舍五入？

1> Zane Bien..：

---

任何给定的两个日期之间的月差异:

我很惊讶这还没有提到:

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

---

谢谢,在我的情况下,我需要包容日期,所以我用"date_add(enddate,interval 1 day)"替换所有"enddate".那么2014-03-01到2014-05-31将给出3.00而不是2.97

---

这是正确的答案,对所选答案的'PERIODDIFF`评论的所有赞成票都非常错过领先

2> Max Caceres..：

---

PERIOD_DIFF计算两个日期之间的月份.

例如,要计算your_table中now()和时间列之间的差异:

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

3> Smolla..：

---

我也使用PERIODDIFF.要获取日期的年份和月份,我使用函数 EXTRACT:

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

---

难以置信!使用'DATE_FORMAT`方法减少50%的时间,谢谢!+1

---

这是不正确的,`PERIOD_DIFF`不占用日期值,因此如果小于整数月,则计算四舍五入到最接近月份的月数.您应该编辑您的答案以明确这一点.

4> SoapBox..：

---

该DATEDIFF功能可以给你的天数两个日期之间.哪个更准确,因为......你怎么定义一个月？(28,29,30或31天？)

---

[PERIODDIFF](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_period-diff)可以准确计算月数.

---

对于日期而言,"更准确"是令人难以置信的主观.例如,如果您所处的月度周期需要知道两个日期之间的月数,那么您可能比上面列出的原因更重要.一个月多久了？如果我需要一个月计算,我不能简单地除以30.

---

这不是正确的答案; 你应该在下面标记[Max's](http://stackoverflow.com/a/562477/389034)回答正确.

---

为什么"PERIODDIFF"评论有这么多的赞成？Perioddiff不占用日值,因此如果小于整数月,您计算的是舍入到最接近月份的月数

5> 小智..：

---

我更喜欢这种方式,因为evryone乍看之下会清楚地理解它:

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

6> IanS..：

---

正如这里的许多答案所示,"正确"的答案完全取决于您的需求.就我而言,我需要四舍五入到最接近的整数.

考虑这些例子: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的答案显然更准确,但对我的喜好来说太冗长了.

7> Vincent Ramd..：

---

从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.

8> Mike Trader..：

---

有没有更好的办法？是.不要使用MySQL时间戳.除了它们占用36个字节之外,它们根本不方便使用.我建议使用朱利安日期和午夜的所有日期/时间值.这些可以组合成一个UnixDateTime.如果它存储在DWORD(无符号4字节整数)中,那么直到2106的日期可以存储为秒,因为epoc,01/01/1970 DWORD max val = 4,294,967,295 - 一个DWORD可以保存136年的秒数

Julian Dates非常适合在进行日期计算时使用UNIXDateTime值在进行日期/时间计算时很有用.两者都不好看,所以当我需要一个我不会做太多计算的列时我使用时间戳有,但我想要一目了然的指示.

转换为Julian和返回可以用一种好的语言很快完成.使用指针我将它降低到大约900个Clks(当然这也是从STRING到INTEGER的转换)

当您进入使用日期/时间信息的严肃应用程序(例如金融市场)时,Julian日期是事实上的.

9> 小智..：

---

查询将如下:

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

从客户记录中创建的日期戳开始,给出了许多日历月,让MySQL在内部进行月份选择.