blocks|key|1474428|text|DATEDIFF函数可以给出两个日期之间的天数。哪个更准确，因为..。您如何定义一个月？(28、29、30或31天？)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1474429|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff^0|0|8|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

The <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff" rel="noreferrer">DATEDIFF</a> 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?)

blocks|key|1689625|text|PERIOD_DIFF计算两个日期之间的月份。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1689626|例如，要计算now()与your_table中的时间列之间的差值：|1689627|select+period_diff(date_format(now(),+'%25Y%25m'),+date_format(time,+'%25Y%25m'))+as+months+from+your_table;|code-block|syntax|javascript|1689628|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_period-diff^0|0|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

<a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_period-diff" rel="noreferrer">PERIOD_DIFF</a> calculates months between two dates.

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

<pre><code>select period_diff(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months from your_table;
</code></pre>

blocks|key|1474915|text|我也使用PERIOD_DIFF。要获取日期的年份和月份，我使用函数EXTRACT|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1474916|++SELECT+PERIOD_DIFF(EXTRACT(YEAR_MONTH+FROM+NOW()),+EXTRACT(YEAR_MONTH+FROM+time))+AS+months+FROM+your_table;|code-block|syntax|javascript|1474917|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_period-diff|1|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract^0|4|B|0|X|7|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

I use also <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_period-diff" rel="nofollow noreferrer">PERIOD_DIFF</a>. To get the year and the month of the date, I use the function <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract" rel="nofollow noreferrer">EXTRACT</a>:
<pre><code> SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;
</code></pre>

blocks|key|1474838|text|我更喜欢这种方式，因为每个人一看就会明白：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1474839|SELECT
++++12+*+(YEAR(to)+-+YEAR(from))+%2B+(MONTH(to)+-+MONTH(from))+AS+months
FROM
++++tab;|code-block|syntax|javascript|1474840|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT
 12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
 tab;
</code></pre>

blocks|key|1474538|text|有没有更好的方法？是。不要使用MySQL时间戳。除了占用36个字节之外，它们使用起来一点也不方便。我建议对所有日期/时间值使用Julian日期和午夜起的秒数。这些可以组合在一起形成一个UnixDateTime。如果将其存储在DWORD+(无符号4字节整数)中，则可以将一直到2106的日期存储为自epoc以来的秒数，1/01/1970+DWORD+max+val+=+4,294,967,295+-+DWORD可以保存136年的秒数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1474539|在进行日期计算时，Julian日期非常好用UNIXDateTime值在进行日期/时间计算时都不好用，所以当我需要一个不会进行太多计算的列时，我会使用时间戳，但我想要一个一目了然的指示。|1474540|在一种好的语言中，转换为Julian并返回可以非常快速地完成。使用指针，我可以将其减少到900个Clks+(当然，这也是从字符串到整数的转换)|1474541|当您进入使用日期/时间信息的严肃应用程序时，例如金融市场，Julian日期是事实。|1474542|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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 

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.

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)

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

blocks|key|1689688|text|查询将类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1689689|select+period_diff(date_format(now(),"%25Y%25m"),date_format(created,"%25Y%25m"))+from+customers+where..|code-block|syntax|javascript|1689690|在客户记录上提供自创建日期戳以来的日历月份数，让MySQL在内部选择月份。|1689691|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The Query will be like:

<pre><code>select period_diff(date_format(now(),"%Y%m"),date_format(created,"%Y%m")) from customers where..
</code></pre>

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

blocks|key|1474848|text|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,+'%25Y%25m'+),+date_format(+pFecha2,+'%25Y%25m'+)+)+;

++/*+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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1474849|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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) &lt; 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
</code></pre>

blocks|key|1474970|text|执行这段代码，它将创建一个函数datedeifference，它将为您提供日期格式yyyy-mm-dd的差异。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1474971|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,+'%25y%25m'),date_format(date2,+'%25y%25m'))DIV+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++PERIOD_DIFF(date_format(date1,+'%25y%25m'),date_format(date2,+'%25y%25m'))%25+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++DATEDIFF(date1,+DATE(CONCAT(YEAR(date1),'-',+MONTH(DATE_SUB(date1,+INTERVAL+1+MONTH)),+'-',+DAY(date2))))),
++++++++++++++++++++++++++++++++++++++++'%25Y-%25m-%25d');
++++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,+'%25y%25m'),date_format(date2,+'%25y%25m'))DIV+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++PERIOD_DIFF(date_format(date1,+'%25y%25m'),date_format(date2,+'%25y%25m'))%25+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++DATEDIFF(date1,+DATE(CONCAT(YEAR(date1),'-',+MONTH(date1),+'-',+DAY(date2))))),
++++++++++++++++++++++++++++++++++++++++'%25Y-%25m-%25d');
++++ELSE
++++++++++++++++SET+dif=DATE_FORMAT(
++++++++++++++++++++++++++++++++++++++++CONCAT(
++++++++++++++++++++++++++++++++++++++++++++PERIOD_DIFF(date_format(date1,+'%25y%25m'),date_format(date2,+'%25y%25m'))DIV+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++PERIOD_DIFF(date_format(date1,+'%25y%25m'),date_format(date2,+'%25y%25m'))%25+12+,+
++++++++++++++++++++++++++++++++++++++++++++'-',
++++++++++++++++++++++++++++++++++++++++++++DATEDIFF(date1,+DATE(CONCAT(YEAR(date1),'-',+MONTH(date1),+'-',+DAY(date2))))),
++++++++++++++++++++++++++++++++++++++++'%25Y-%25m-%25d');
++++END+IF;

RETURN+dif;
END+$$
DELIMITER;|code-block|syntax|javascript|1474972|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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)))) &lt; 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)))) &lt; 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;
</code></pre>

blocks|key|1689434|text|SELECT+*+
FROM+emp_salaryrevise_view+
WHERE+curr_year+Between+'2008'+AND+'2009'+
++++AND+MNTH+Between+'12'+AND+'1'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1689435|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * 
FROM emp_salaryrevise_view 
WHERE curr_year Between '2008' AND '2009' 
 AND MNTH Between '12' AND '1'
</code></pre>

blocks|key|1690103|text|我需要精确的月差。尽管Zane+Bien的解决方案在正确的方向上，但他的第二个和第三个例子给出了不准确的结果。二月份的一天除以二月份的天数并不等于五月份的一天除以五月份的天数。因此，第二个示例应该输出((31-5%2B1)/31+%2B+13/30+=)+1.3043，第三个示例((29-27%2B1)/29+%2B+2/30+%2B3=)+3.1701。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1690104|我以以下查询结束：|1690105|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))+%2B+INTERVAL+1+DAY))+/+DAY(LAST_DAY((SELECT+startdate))))+AS+period1,+++++
++++TIMESTAMPDIFF(MONTH,+LAST_DAY((SELECT+startdate))+%2B+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)+%2B+(SELECT+period2)+%2B+(SELECT+period3)+AS+months|code-block|syntax|javascript|1690106|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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. 

I ended up with the following query:

<pre><code>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
</code></pre>

blocks|key|1475009|text|您可以通过以下方式获取年、月和日：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1475010|SELECT+
username
,date_of_birth
,DATE_FORMAT(CURDATE(),+'%25Y')+-+DATE_FORMAT(date_of_birth,+'%25Y')+-+(DATE_FORMAT(CURDATE(),+'00-%25m-%25d')+<+DATE_FORMAT(date_of_birth,+'00-%25m-%25d'))+AS+years
,PERIOD_DIFF(+DATE_FORMAT(CURDATE(),+'%25Y%25m')+,+DATE_FORMAT(date_of_birth,+'%25Y%25m')+)+AS+months
,DATEDIFF(CURDATE(),date_of_birth)+AS+days
FROM+users|code-block|syntax|javascript|1475011|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can get years, months and days this way:

<pre><code>SELECT 
username
,date_of_birth
,DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(CURDATE(), '00-%m-%d') &lt; 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
</code></pre>

blocks|key|1475095|text|您还可以尝试执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1475096|select+MONTH(NOW())-MONTH(table_date)+as+'Total+Month+Difference'+from+table_name;|code-block|syntax|javascript|1475097|或|1475098|select+MONTH(Newer_date)-MONTH(Older_date)+as+'Total+Month+Difference'+from+table_Name;|1475099|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can also try this:

<pre><code>select MONTH(NOW())-MONTH(table_date) as 'Total Month Difference' from table_name;
</code></pre>

OR

<pre><code>select MONTH(Newer_date)-MONTH(Older_date) as 'Total Month Difference' from table_Name;
</code></pre>

blocks|key|1475148|text|给定开始日期为ins_frm，结束日期为ins_to的简单答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1475149|SELECT+convert(TIMESTAMPDIFF(year,+ins_frm,+ins_to),UNSIGNED)+as+yrs,
+++++++mod(TIMESTAMPDIFF(MONTH,+ins_frm,+ins_to),12)+mnths
FROM+table_name|code-block|syntax|javascript|1475150|享受：))|1475151|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Simple answer given start date as ins_frm and end date as ins_to

<pre><code>SELECT convert(TIMESTAMPDIFF(year, ins_frm, ins_to),UNSIGNED) as yrs,
 mod(TIMESTAMPDIFF(MONTH, ins_frm, ins_to),12) mnths
FROM table_name
</code></pre>

Enjoy :)))

blocks|key|1475163|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1475164|SELECT+YEAR(end_date)*12+%2B+MONTH(end_date)+-+(YEAR(start_date)*12+%2B+MONTH(start_date))|code-block|syntax|javascript|1475165|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this

<pre><code>SELECT YEAR(end_date)*12 + MONTH(end_date) - (YEAR(start_date)*12 + MONTH(start_date))
</code></pre>

blocks|key|1689778|text|此查询适用于我：)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1689779|SELECT+*+FROM+tbl_purchase_receipt
WHERE+purchase_date+BETWEEN+'2008-09-09'+AND+'2009-09-09'|code-block|syntax|javascript|1689780|它简单地获取两个日期并检索它们之间的值。|1689781|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This query worked for me:)

<pre><code>SELECT * FROM tbl_purchase_receipt
WHERE purchase_date BETWEEN '2008-09-09' AND '2009-09-09'
</code></pre>

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

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?

The difference in months between dates in MySQL

MySQL

我希望计算两个日期之间的日期和时间字段的月数。还有比获取unix时间戳、除以2592000(秒)并在MySQL中四舍五入更好的方法吗？

问MySQL中日期之间的月份差异
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中日期之间的月份差异EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中日期之间的月份差异
EN