MySQL中日期之间的月份差异

I'm looking to calculate the number of months between 2 date time fields.

我在计算两个日期时间域之间的月数。

Is there a better way than getting the unix timestamp and the dividing by 2 592 000 (seconds) and rounding up whithin MySQL?

有比获得unix时间戳和除以2592,000(秒)和四舍五入漂亮的MySQL更好的方法吗?

17 个解决方案

#1

The DATEDIFF function can give you the number of days between two dates. Which is more accurate, since... how do you define a month? (28, 29, 30, or 31 days?)

DATEDIFF函数可以给出两个日期之间的天数。这更准确，因为……你如何定义一个月?(28天、29天、30天还是31天?)

#2

Month-difference between any given two dates:

I'm surprised this hasn't been mentioned yet:

我很惊讶这还没有被提及:

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')
-- 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

It basically gets the number of months elapsed from the first date in the parameter list. This solution automatically compensates for the varying amount of days in each month (28,30,31) as well as taking into account leap years — you don't have to worry about any of that stuff.

它基本上是从参数列表中的第一个日期开始计算的月数。这个解决方案会自动补偿每个月的不同天数(28,30,31)以及考虑闰年——您不必担心这些事情。

---

Month-difference with precision:

It's a little more complicated if you want to introduce decimal precision in the number of months elapsed, 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

PERIOD_DIFF calculates months between two dates.

PERIOD_DIFF计算两个日期之间的月数。

For example, to calculate the difference between now() and a time column in your_table:

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

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

#4

I use also PERIODDIFF. To get the year and the month of the date, I use the function EXTRACT:

我也使用PERIODDIFF。为了得到年份和月份，我使用函数摘录:

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

#5

From the MySQL manual:

从MySQL手册:

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或YYYYMM。注意，周期参数P1和P2不是日期值。

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

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

So it may be possible to do something like this:

所以有可能这样做:

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;

Where d1 and d2 are the date expressions.

d1和d2是日期表达式。

I had to use the if() statements to make sure that the months was a two digit number like 02 rather than 2.

我必须使用if()语句来确保月份是像02这样的两位数，而不是2。

#6

I prefer this way, because evryone will understand it clearly at the first glance:

我喜欢这样，因为evryone一眼就能看清楚:

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

#7

Is there a better way? yes. Do not use MySQL Timestamps. Apart from the fact that they occupy 36 Bytes, they are not at all convenient to work with. I would reccomend using Julian Date and Seconds from midnight for all date/time values. These can be combined to form a UnixDateTime. If this is stored in a DWORD (unsigned 4 Byte Integer) then dates all the way up to 2106 can be stored as seconds since epoc, 01/01/1970 DWORD max val = 4,294,967,295 - A DWORD can hold 136 years of Seconds

有更好的方法吗?是的。不要使用MySQL时间戳。除了它们占用了36个字节之外，它们使用起来一点都不方便。我将使用Julian Date和Seconds从午夜开始重新计算所有日期/时间值。它们可以组合成一个UnixDateTime。如果它存储在一个DWORD(无符号的4字节整数)中，那么从epoc, 01/01/1970 DWORD max val = 4,294,967,295 -一个DWORD可以存储136秒

Julian Dates are very nice to work with when making date calculations UNIXDateTime values are good to work with when making Date/Time calculations Neither are good to look at, so I use the Timestamps when I need a column that I will not be doing much calculation with, but I want an at-a-glance indication.

朱利安日期是很好的一起工作时日期计算UNIXDateTime值好工作时制造日期/时间的计算都是很好的,所以我当我需要使用时间戳列,我不会做太多的计算,但我希望我的想法。

Converting to Julian and back can be done very quickly in a good language. Using pointers I have it down to about 900 Clks (This is also a conversion from a STRING to an INTEGER of course)

用一种好的语言可以很快地转换到Julian和back。使用指针，我将它降低到大约900个clk(这也是一个从字符串到整数的转换)

When you get into serious applications that use Date/Time information like for example the financial markets, Julian dates are de-facto.

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

#8

The Query will be like:

查询将如下:

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

Gives a number of calendar months since the created datestamp on a customer record, letting MySQL do the month selection internally.

由于客户记录上创建的datestamp，允许MySQL在内部进行月选择，因此给出了几个日历月。

#9

As many of the answers here show, the 'right' answer depends on exactly what you need. In my case, I need 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日- 2月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年。赞恩的答案显然更准确，但我喜欢它太啰嗦。

#10

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

#11

Execute this code and it will create a function datedeifference which will give you the difference in date format yyyy-mm-dd.

执行这段代码，它将创建一个函数datedeifference，它将给您带来日期格式yyyyyyy -mm-dd的差异。

DELIMITER $$

CREATE FUNCTION datedifference(date1 DATE, date2 DATE) RETURNS DATE
NO SQL

BEGIN
    DECLARE dif DATE;
    IF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < 0    THEN
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(DATE_SUB(date1, INTERVAL 1 MONTH)), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    ELSEIF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < DAY(LAST_DAY(DATE_SUB(date1, INTERVAL 1 MONTH))) THEN
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    ELSE
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    END IF;

RETURN dif;
END $$
DELIMITER;

#12

This depends on how you want the # of months to be defined. Answer this questions: 'What is difference in months: Feb 15, 2008 - Mar 12, 2009'. Is it defined by clear cut # of days which depends on leap years- what month it is, or same day of previous month = 1 month.

这取决于你想要多少个月的定义。回答以下问题:“月份有什么不同:2008年2月15日- 2009年3月12日”。它的定义是明确的天数，这取决于闰年——是哪个月，还是前一个月的同一天= 1个月。

A calculation for Days:

天的计算:

Feb 15 -> 29 (leap year) = 14 Mar 1, 2008 + 365 = Mar 1, 2009. Mar 1 -> Mar 12 = 12 days. 14 + 365 + 12 = 391 days. Total = 391 days / (avg days in month = 30) = 13.03333

2月15日- >29(闰年)= 2008年3月1日14日+ 365 = 2009年3月1日。3月1日-> 3月12日= 12天。14 + 365 + 12 = 391天。总天数= 391天/ (avg天数= 30天)= 13.03333天

A calculation of months:

个月的计算:

Feb 15 2008 - Feb 15 2009 = 12 Feb 15 -> Mar 12 = less than 1 month Total = 12 months, or 13 if feb 15 - mar 12 is considered 'the past month'

2008年2月15日- 2009年2月15日- 2009年2月12日- 3月12日

#13

SELECT * 
FROM emp_salaryrevise_view 
WHERE curr_year Between '2008' AND '2009' 
    AND MNTH Between '12' AND '1'

#14

I needed month-difference with precision. Although Zane Bien's solution is in the right direction, his second and third examples give inaccurate results. A day in February divided by the number of days in February is not equal to a day in May divided by the number of days in May. So the second example should output ((31-5+1)/31 + 13/30 = ) 1.3043 and the third example ((29-27+1)/29 + 2/30 + 3 = ) 3.1701.

我需要精确的月差。虽然Zane Bien的解决方案是正确的，但是他的第二个和第三个例子给出了不准确的结果。2月份的一天除以2月份的天数不等于5月份的天数除以5月份的天数。所以第二个例子应该输出(31-5+1)/31 + 13/30 =)1.3043，第三个例子(29-27+1)/29 + 2/30 + 3 =)3.1701。

I ended up with the following query:

最后我得到了以下查询:

SELECT
    '2012-02-27' AS startdate,
    '2012-06-02' AS enddate,
    TIMESTAMPDIFF(DAY, (SELECT startdate), (SELECT enddate)) AS days,
    IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), 0, (TIMESTAMPDIFF(DAY, (SELECT startdate), LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY)) / DAY(LAST_DAY((SELECT startdate)))) AS period1,     
    TIMESTAMPDIFF(MONTH, LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY, LAST_DAY((SELECT enddate))) AS period2,
    IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), (SELECT days), DAY((SELECT enddate))) / DAY(LAST_DAY((SELECT enddate))) AS period3,
    (SELECT period1) + (SELECT period2) + (SELECT period3) AS months

#15

You can get years, months and days this way:

你可以用这样的方式获得年、月、日:

SELECT 
username
,date_of_birth
,DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS years
,PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m') , DATE_FORMAT(date_of_birth, '%Y%m') ) AS months
,DATEDIFF(CURDATE(),date_of_birth) AS days
FROM users

#16

You can also try this:

你也可以试试:

select MONTH(NOW())-MONTH(table_date) as 'Total Month Difference' from table_name;

OR

或

select MONTH(Newer_date)-MONTH(Older_date) as 'Total Month Difference' from table_Name;

#17

This query worked for me:)

这个查询对我有用:)

SELECT * FROM tbl_purchase_receipt
WHERE purchase_date BETWEEN '2008-09-09' AND '2009-09-09'

It simply take two dates and retrieves the values between them.

它只需要两个日期并检索它们之间的值。