How to get difference between two dates in months using MySQL query?

Question

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 then dividing by 2 592 000 (seconds) and rounding up within MySQL?

Screamer

Answer 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?)

Smokedry

PERIODDIFF can calculate the number of months accurately. – Scroop

"More accurate" is incredible subjective when it comes to dates. If, for example, you are in a business that has monthly cycles needing to know how many months are between two dates can be more important than the number of days for the very reason you lay out above. How long is a month? If I need a month count I can't simply divide by 30. – Roseboro

This is not the correct answer; you should mark Max's answer correct, below. – Noteworthy

Max's answer is not correct if you want the number of whole months, accounting correctly for leap years and the differing number of days in a month. PERIODDIFF simply takes a YYYYMM value, so unless you account for the days yourself before you pass the YYYYMM values, then all you're calculating is the number of months, rounded up to the nearest month if there is less than a whole number of months. See Zane's answer below. – Brahms

Why are there so many upvotes for the PERIODDIFF comment? Perioddiff doesn't take day values, so you're calculating the number of months rounded up to the nearest month if there is less than a whole number of months – Brahms

Oh, how I love these kind of answers. Instead of answer it trying to explain somebody how the world works. 18 up and marked as the answer. I dont understand some guys here... – Significs

Days can have leap seconds, so only seconds are precise. Most contracts don't use seconds. Ah, by the way, you didn't help to answer the question... – Besmirch

PERIOD_DIFF rounds the number of months. If it is 17th Feb, 1st Jan is considered 1 month ago. When I would say that it is greater than 1 month. So to say Max's is correct is not necessarily true. – Unconscious

This question was answered in a different thread in a more relevant fashion: #5634321 – Drogue

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

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.

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.

---

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:

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

Unoccupied

This is the correct answer, all the upvotes for the PERIODDIFF comment on the selected answer are very miss-leading – Brahms

Thanks, in my case I needed inclusive dates, so I replaces all "enddate" with "date_add(enddate,interval 1 day)". Then 2014-03-01 to 2014-05-31 will give 3.00 instead of 2.97 – Soldier

Thank you so much.....special thanx for "Month-difference with precision:". You are the star of mysql – Prepositive

Agreed. This is the correct answer. Exactly what I was looking for! Thanks. – Decorum

If I manually interpret your example startdate='2012-05-05' and enddate='2012-06-13' then you will see 27 days out of 31 in May and 12 day out of 30 days in June, So it should have outputted 0,87097 + 0.4 = 1,27097 and your output gives 1.2667. See also my answer on this page for exact precision calculation. – Miltie

Answer 3

PERIOD_DIFF calculates months between two dates.

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

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

Scroop

Any idea what it does when it could be 11 months and 1 day, so 12 months, but if you change to 30 day months then it'd still be 11? – Screamer

11 months and 1 day would return 11 months with the example above. PERIOD_DIFF has no sense of 30 vs 31 day months. – Scroop

Answer 4

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

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

Chamness

Incredible! It use 50% less time that DATE_FORMAT method, thanks! +1 – Mutton

Here for a unix timestamp SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(time, "%Y%m%d"))) AS months FROM your_table; – Chamness

This is incorrect, PERIOD_DIFF doesn't take day values, so you're calculating the number of months rounded up to the nearest month if there is less than a whole number of months. You should edit your answer to make this clear. – Brahms

Yes, to elaborate on the warning of using this method: it gives the number of calendar months the time period crosses into from the current month. For example, if the time difference is 5 days but both dates are in the same month, the result will be 0 months. If the time difference is 5 days but crosses from September to October, it will be 1 month. – Stockbroker

Perfect for me! Counts only turns of month and this is what I need. – Hubble

Answer 5

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.

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

Albanese

I disagree... SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-02-04'); gives you 0 months apart... SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-03-01'); gives you 1... if 5 days is 0, 29 days is 1? – Waine

@Scott, there is one whole month (called February) between January and March. TIMESTAMPDIFF just gives the number of complete months between the dates. If you want any month-sized period to equal one month, use the rounded version I have given. – Albanese

Answer 6

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;

Gametophore

Stanislav - this method is exactly right if you want to know data BY MONTH NUMBER. I have some reports that show sales by month (Jan/Feb/Mar etc) but there is so much data (charts by quarter/year/past years etc) that I can't group by anything. I need to pull in the raw data and loop through it - when it is 1/31, many of the calculations put "next month" into March when us humans know it is Feb. IF it is 2/4, some calculations say that 1/31 is "this month" but 1/28 was "last month" – Waine

Answer 7

From the MySQL manual:

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

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.

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

Gens

Answer 8

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.

Aposematic

Where did you get the info about the fact they occupy 36 bytes? MySQL manual states storage requirement for TIMESTAMP column is 4 bytes. dev.mysql.com/doc/refman/5.1/en/storage-requirements.html – Eggplant

start planning for the Y2106 bug now! ;-) – Kerakerala

Answer 9

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.

Belting

Answer 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

Dichromaticism

Answer 11

Execute this code and it will create a function datedeifference which will give you the difference in date format yyyy-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;

Latishalatitude

Do you think you'd could clean up and comment your code a bit so it's a little more understandable? Thanks – Screamer

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

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

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'

Nubile

Answer 13

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

Enlarger

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

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

Realty

Answer 15

PERIOD_DIFF() function

One of the way is MySQL PERIOD_DIFF() returns the difference between two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not date values.

Code:

SELECT PERIOD_DIFF(200905,200811);

Procrastinate

Answer 16

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

Gotten

Answer 17

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;

Brigandage

Answer 18

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

SELECT convert(TIMESTAMPDIFF(year, ins_frm, ins_to),UNSIGNED) as yrs,
       mod(TIMESTAMPDIFF(MONTH, ins_frm, ins_to),12) mnths
FROM table_name

Enjoy :)))

Spartacus

Answer 19

Try this

SELECT YEAR(end_date)*12 + MONTH(end_date) - (YEAR(start_date)*12 + MONTH(start_date))

Peddle

Welcome to Stack Overflow! Make sure to check the date on questions and whether existing answers have the same solution. – Staats

Answer 20

Although it's an old topic it shows on top in google and I don't see newer questions related to Mysql to calculate the difference in months. And I needed a very precise calculation including the fraction of the month.

This for the purpose to calculate a subscription fee e.g. 8 euro per month. Then 1 day in februari does have a different price compared to other months. So the fraction of months needs to be calculated and here the precision of the fraction is based on seconds.

What it does is to split the calculation into 3 parts when calculation between @from and @to dates:

1. fraction of the calendar month between @from and the end of the @from calendar month
2. number of whole calendar months between @from and @to
3. fraction of the calendar month between start of the calendar month and @to

E.g from '2021-09-29 12:00:00' to '2021-11-07 00:00:00':

1. The 1.5 days at the end of september 2021. September does have 30 days so the fraction is 0.05 month (1.5/30).
2. the whole month oktober 2021 so 1 full month
3. The 6 full days at the begin of november 2021. November does have 30 days so the faction is 0.2 month (6/30).

So the outcome is 1.25 month.

set @from  = '2021-09-29 12:00:00';
set @to    = '2021-11-07 00:00:00';
select 
/* part 1 */ (unix_timestamp(last_day(@from)) + 86400 - unix_timestamp(@from)) / 86400 / day(last_day(@from))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM @to), EXTRACT(YEAR_MONTH FROM @from)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(@to)) + 86400 - unix_timestamp(@to)) / 86400 / day(last_day(@to)) 
month_fraction;

Exactly the same calculation but now based on fields for people not using mysql variables and easier to take over your own fields:

select 
/* part 1 */ (unix_timestamp(last_day(periodStart)) + 86400 - unix_timestamp(periodStart)) / 86400 / day(last_day(periodStart))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM periodTill), EXTRACT(YEAR_MONTH FROM periodStart)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(periodTill)) + 86400 - unix_timestamp(periodTill)) / 86400 / day(last_day(periodTill))
month_fraction
from (select '2021-09-29 12:00:00' periodStart, '2021-11-07 00:00:00' periodTill) period

For speed optimization I've used unix_timestamp which should perform fast as it is able to use mathematic calculation. The unix_timestamp returns a number in seconds. The 86400 is the number of seconds in a day.

Miltie

Answer 21

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.

Kwangchowan

totally incorrect answer, he wants a count of months – Dean