Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

The difference in months between dates in 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?

like image 880
Darryl Hein Avatar asked Nov 14 '08 01:11

Darryl Hein


People also ask

How do I find the difference in months between two dates in MySQL?

PERIOD_DIFF() function 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.

How can I get the difference between two dates in MySQL?

MySQL DATEDIFF() Function The DATEDIFF() function returns the number of days between two date values.

How do I calculate the number of months between two dates in SQL?

The following statement calculates the months between two specified dates: SQL> SELECT MONTHS_BETWEEN 2 (TO_DATE('02-02-2015','MM-DD-YYYY'), 3 TO_DATE('12-01-2014','MM-DD-YYYY') ) "Months" 4 FROM DUAL;.


1 Answers

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 
like image 61
Zane Bien Avatar answered Sep 22 '22 15:09

Zane Bien