How to Compute Years, Months and Days Between Two Dates in MySQL (without defining a function)-CodePudding

My goal is to use MySQL to calculate the number of years, months and days between two dates without defining a function/stored procedure. The desired output would be "12y 5m 3d" (twelve years, five months, three days).

I've studied multiple earlier posts, and I'm close but struggling with the "days" component.

So far I have:

SET @ReferenceDate = '1988-06-04';

SELECT CONCAT (  FLOOR( (TIMESTAMPDIFF(MONTH, @ReferenceDate, CURDATE()) / 12) ), 'y ',
                 MOD(    TIMESTAMPDIFF(MONTH, @ReferenceDate, CURDATE()) , 12)  , 'm ',
                 /* not sure what function to use for the remaining "days" portion */   'd '
               ) AS Duration;

I believe I can use the DATEDIFF(date1, date2) to get the number of dates, but I am struggling to configure the correct second argument so it only calculates the days in the final partial month (not the number of days since inception).

Any guidance on this point would be greatly appreciated. Thank you!

CodePudding user response：

The problem you are encountering is you could have any day as the reference basis, and the date you run could be any other day. Ex: a date basis of 19th of the month, but today is the 4th. What you need to do is bring the NOW date back however many days of the base date so the base date is now in effect the first of its month.

So, reference date (just to be different than the 4th which is same as current date)

RefDate = '1988-06-18' (June 18th). So, if we bring this date back to the first of the month, we are subtracting 17 days. Now, take whatever TODAY is and bring IT back 17 days so it is offset by the same day shift. Sept 4th - 17 days gets you to Aug 18th. Now, you have a 1st-of-the-month date correlation to the same date-shift as today. So your # of days is now 18. Your years and month computations looked to be ok.

By doing a DATE_ADD() based on 1 - dayofmonth( @ReferenceDate ) gets you to first of the month. Ex: 18th becomes a date add of 1-18 = negative 17, thus rolling BACK 17 days.

Here is a sample of such output.

select
       FLOOR( (TIMESTAMPDIFF(MONTH, subset.baseDate, subset.DateShift) / 12) ) difYears,
       MOD(  TIMESTAMPDIFF(MONTH, subset.baseDate, subset.DateShift) , 12) difMonths,
       DAYOFMONTH( subset.DateShift ) difDays
   from
      ( select 
             dayofmonth( @ReferenceDate ) -1 as daysOffset,
             date_add( @ReferenceDate, INTERVAL 1-dayofmonth( @ReferenceDate ) DAY ) as baseDate,
             date_add( curdate(), INTERVAL 1-dayofmonth( @ReferenceDate ) DAY ) as DateShift
          from 
             ( select @ReferenceDate := '1988-06-08' ) sqlvars ) subset

If you are trying to pull all records for some given criteria, just change the inner query to something like

select
       subset.*,
       FLOOR( (TIMESTAMPDIFF(MONTH, subset.baseDate, subset.DateShift) / 12) ) difYears,
       MOD(  TIMESTAMPDIFF(MONTH, subset.baseDate, subset.DateShift) , 12) difMonths,
       DAYOFMONTH( subset.DateShift ) difDays
   from
      ( select 
             dayofmonth( yt.SomeDateColumn ) -1 as daysOffset,
             date_add( yt.SomeDateColumn, INTERVAL 1-dayofmonth( yt.SomeDateColumn ) DAY ) as baseDate,
             date_add( curdate(), INTERVAL 1-dayofmonth( yt.SomeDateColumn ) DAY ) as DateShift,
             yt.OtherColumnsYouMayWant
          from 
             YourTable yt
          where 
             yt.SomeColumnCondition = 'whatever' ) subset

So now, you can get MANY records with each of their respective reference date basis, with its corresponding daysOffset computedd and its current date shifted by said days all in a pre-query. THEN pull your years/months/days from that result.

CodePudding user response：

Heavily based on this answer, and also accounting for @ReferenceDate possibly being in the future (hence the use of ABS()):

SET @ReferenceDate = '1988-06-04';

SELECT 
CONCAT(
  ABS(DATE_FORMAT( CURDATE(),  '%Y' ) - DATE_FORMAT( @ReferenceDate,  '%Y' )), 'y',
  ABS(DATE_FORMAT( CURDATE(),  '%m' ) - DATE_FORMAT( @ReferenceDate,  '%m' )), 'm',
  ABS(DATE_FORMAT( CURDATE(),  '%d' ) - DATE_FORMAT( @ReferenceDate,  '%d' )), 'd'
) AS Duration