The difference in months between dates in MySQL

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

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

Answer 2

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'

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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;

Answer 7

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.

Answer 8

I want to get the names of the months between two date. can anyone please help me ? thanks you.

Answer 9

See my post at: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Look for post of May 5 2008 3:48pm.

Answer 10

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.

Answer 11

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;