How to Calculate Precise Month Differences in MySQL?

Precise Month Difference Calculation in MySQL

MySQL provides an indispensable function, TIMESTAMPDIFF(), to calculate the number of months between two dates. It eliminates complications associated with varying month lengths and leap years.

SELECT TIMESTAMPDIFF(MONTH, '2012-03-08', '2012-08-01');
-- Outputs: 4

Precision Enhancement

For greater precision, a more intricate approach can be employed:

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
  )

This formula adds precision by calculating the remainder days (after month difference) as a fraction of a month.

Examples

WITH startdate AS '2012-03-08', enddate AS '2012-04-15'
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
  );
-- Outputs: 1.2333

This result indicates that there are 1 month and approximately 23.33% of a month difference between the two dates.

The above is the detailed content of How to Calculate Precise Month Differences in MySQL?. For more information, please follow other related articles on the PHP Chinese website!