How to Calculate Precise Month Differences in MySQL?-Mysql Tutorial-php.cn

How to Calculate Precise Month Differences in MySQL?

Linda Hamilton

Release： 2024-11-25 14:14:11

Original

510 people have browsed it

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

Copy after login

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
  )

Copy after login

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

Copy after login

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!