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?

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