Расчет периодов стажа в MySQL

На одном форуме задали вопрос о том, как правильно посчитать разность дат в MySQL для учета стажа сотрудника. На первый взгляд вопрос оказался простым, но при детальном рассмотрении все оказалось куда интереснее.

Проблема

Для начала я поговорил с парой знакомых кадровиков и расспросил их о том, как же у нас в стране принято считать период страхового стажа сторудника. Как оказалось не все так просто. По законодателсьвту периоды стажа считаются так: каждые 30 дней переводятся в месяцы, а кажде 12 месяцев — в годы. Но тем не менее, широко распространен учет страхового стажа по другой схеме — полные месяцы и годы учитываются как есть, а дни, составляющие неполные начальный и конечне месяцы складываются и переводятся в месяцы по 30-ть дней. Вот про релизацию последнего способа я и хочу рассказать.

Решение

Я решил оформить этот алгоритм в виде хранимой функции с двумя входными параметрами — начальной и конечной датой. Результатом же работы будет строка dd.mm.yy, хотя для разницы дат лучше подходит формат «y лет, m месяцев, d дней». Формирование удобочитаемой строки я опутсил в этом топике дабы сберечь место и не отвлекаться на тривиальные задачи. Я разбил все возможные вариации входных параметров на два случая: первый — даты находятся в одном месяце, и второй — в разынх. В первом случае разница дат будет просто перводится в дни. Во втором же случае необходимо вычислить разность в месяцах между датами (минус один месяц), а так же сумму недостающих до конца месяца дней в начальной дате и день месяца в конечной дате. Код получился такой:

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;

Второй вопрос

Для того чтоб узнать один рабочий период, этой функции вполне достаточно, но что делать если нужно по этому же алгоритму посчитать сумму нескольких рабочих периодов. Для этого нужно будет сделать уже три хранимых процедуры: первая (fCountFullMonths) будет вычилсять кол-во полных месяцев в указанном периоде, вторая (fCountCutOffDays) — будет вычилсять кол-во неучтенных первой функцией дней (в начале и конце периода), и третья (fFormatPeriod) — будет принимать кол-во месяцев в качестве перового параметра и кол-во дней в качестве второго, и будет форматировать эти данные в строку удобную для вывода, с учетом перевода каждых 30-ти дней в месяцы, а 12 месяцев — в годы.
С этими функциями посчитать общий стаж сотрудника не составит труда:

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

Реализацию этих функций здесь приводить не буду, т.к. их легко составить «разобрав на куски» функцию выше.