Calculation of periods of experience in MySQL

At one forum they asked a question about how to correctly calculate the difference in dates in MySQL to account for the experience of an employee. At first glance, the question turned out to be simple, but on closer examination, everything turned out to be much more interesting.

Problem

To begin with, I spoke with a couple of familiar personnel officers and asked them about how, in our country, it is considered to be the period of insurance service of an employee. As it turned out not so simple. According to the legislation, periods of service are considered as follows: every 30 days are converted into months, and every 12 months - into years. But nevertheless, accounting for insurance experience according to a different scheme is widespread - full months and years are counted as they are, and the days that make up the incomplete initial and final months are added up and converted into months of 30 days. That's about the release of the latter method, and I want to tell.

Decision

I decided to design this algorithm as a stored function with two input parameters - the start and end date. The result of the work will be the string dd.mm.yy, although for the date difference the format “y years, m months, d days” is better suited. Formation of a readable line, I oputil in this topic in order to save space and not be distracted by trivial tasks. I have broken all possible variations of input parameters into two cases: the first - the dates are in one month, and the second - in different. In the first case, the date difference will be simply transferred to the days. In the second case, it is necessary to calculate the difference in months between the dates (minus one month), as well as the sum of the days missing in the beginning of the month in the starting date and the day of the month in the ending date. The code is:

CREATE FUNCTION `fNaturalDateDiff` ( iStartDate DATE , iFinalDate DATE )
RETURNS varchar ( 255 )
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE aDays , aMonths INTEGER ;

IF DATE_FORMAT ( iStartDate , ' % Y % m' ) = DATE_FORMAT ( iFinalDate , ' % Y % m' ) THEN
SET aDays = DATEDIFF ( iFinalDate , iStartDate ) + 1 ;
SET aMonths = 0 ;
ELSE
SET aDays = DATEDIFF ( LAST_DAY ( iStartDate ) , iStartDate ) + DAYOFMONTH ( iFinalDate ) + 1 ;
SET aMonths = PERIOD_DIFF ( DATE_FORMAT ( iFinalDate , ' % Y % m' ) , DATE_FORMAT ( iStartDate , ' % Y % m' ) ) - 1 ;
END IF ;
SET aMonths = aMonths + aDays DIV 30 ;
SET aDays = aDays % 30 ;
RETURN CONCAT ( LPAD ( aDays , 2 , '0' ) , '.' , LPAD ( aMonths % 12 , 2 , '0' ) , '.' , LPAD ( aMonths DIV 12 , 2 , '0' ) ) ;
END ;

Second question

In order to find out one working period, this function is quite enough, but what to do if you need to calculate the sum of several working periods using the same algorithm. To do this, you will need to do three stored procedures: the first (fCountFullMonths) will deduct the number of complete months in the specified period, the second (fCountCutOffDays) will deduct the number of days unaccounted for by the first function (at the beginning and end of the period), and the third ( fFormatPeriod) - will take the number of months as the first parameter and the number of days as the second, and will format this data into a string convenient for output, taking into account the translation of every 30 days into months, and 12 months into years .
With these functions, it is not difficult to calculate the total experience of an employee:

SELECT Person , fFormatPeriod ( SUM ( fCountFullMonths ( StartDate , EndDate ) ) , SUM ( fCountCutOffDays ( StartDate , EndDate ) ) ) FROM experience WHERE Person = ...

I will not give here the implementation of these functions, since it is easy to compose them by “disassembling into pieces” the function above.