MySQL MySQL中日期之间的月差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/288984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The difference in months between dates in MySQL
提问by Darryl Hein
I'm looking to calculate the number of months between 2 date time fields.
我正在寻找计算 2 个日期时间字段之间的月数。
Is there a better way than getting the unix timestamp and the dividing by 2 592 000 (seconds) and rounding up whithin MySQL?
有没有比获取 unix 时间戳和除以 2 592 000（秒）并在 MySQL 中四舍五入更好的方法？
采纳答案by SoapBox
回答by Zane Bien
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 TIMESTAMPor DATETIMEvalues (or even DATEas MySQL will auto-convert) as well as the unit of time you want to base your difference on.
这允许您做的是传递两个TIMESTAMP或DATETIME值（甚至DATEMySQL 会自动转换）以及您希望基于差异的时间单位。
You can specify MONTHas the unit in the first parameter:
您可以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
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 startdateand enddateare 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
回答by Max Caceres
PERIOD_DIFFcalculates 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;
回答by Smolla
I use also PERIODDIFF. To get the year and the month of the date, I use the function EXTRACT:
我也使用PERIODDIFF。要获取日期的年份和月份，我使用了EXTRACT函数 ：
SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;
回答by IanS
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 月 1 日 - > 2 月 1 日？整整1个月，正好1个月。
To get the number of whole (complete)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 roundedduration 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 年。赞恩的回答显然更准确，但我不喜欢它太冗长了。
回答by Vincent Ramdhanie
From the MySQL manual:
从 MySQL 手册：
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.
mysql> SELECT PERIOD_DIFF(200802,200703); -> 11
PERIOD_DIFF(P1,P2)
返回期间 P1 和 P2 之间的月数。P1 和 P2 的格式应为 YYMM 或 YYYYMM。请注意，期间参数 P1 和 P2 不是日期值。
mysql> SELECT 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。
回答by Stanislav Basovník
I prefer this way, because evryone will understand it clearly at the first glance:
我更喜欢这种方式，因为每个人第一眼就会明白：
SELECT
12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
tab;
回答by Mike Trader
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 字节整数）中，则可以将一直到 2106 的日期存储为自 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.
Julian Dates 在进行日期计算时非常适合使用 UNIXDateTime 值在进行日期/时间计算时很适合使用 两者都不好看，所以当我需要一个我不会做太多计算的列时，我使用 Timestamps与，但我想要一目了然的指示。
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 并返回可以用一门好的语言非常快速地完成。使用指针，我将其降低到大约 900 个时钟（当然，这也是从 STRING 到 INTEGER 的转换）
When you get into serious applications that use Date/Time information like for example the financial markets, Julian dates are de-facto.
当您进入使用日期/时间信息的严肃应用程序（例如金融市场）时，儒略日期是事实上的。
回答by Mike Trader
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.
在客户记录上提供自创建日期戳以来的多个日历月，让 MySQL 在内部进行月份选择。
回答by Rama
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
