DANISH Leap Year Arithmetic in Oracle Database

Let's start with the theory

Federal Law No. 107 “On the Calculation of Time” Article 2 states:

5) calendar year – a period of time from January 1 to December 31, lasting three hundred sixty-five or three hundred sixty-six (leap year) calendar days. The calendar year has a serial number in accordance with the Gregorian calendar;

It is logical and obvious when considering the period from the beginning to the end of the year.

But how many days should there be in the interval (365/366), which starts not just on January 1, but also includes February of a leap year?

The closest answer in meaning was found in the comments to Article 16 of Federal Law 229 “On Enforcement Proceedings” (if anyone has more precise data, please write in the comments):

a period calculated in years ends on the corresponding month and day of the last year of the established period (part 1);

period calculated months, ends on the corresponding day of the last month of the established period. If ending the period, calculated in months, falls on monthwhich is the corresponding number does not havethen the term ends in last day of this months (Part 2; for example, a monthly period calculated from January 29, 30 or 31 will expire on February 28 or 29 in a leap year);

What is written also does not indicate what logic there should be on boundary conditions. There is only one conclusion: If the new date does not contain the required day, then the closest previous date is taken.

I would like to draw your attention to the fact that tax and accounting records are kept by days, not months/years. This makes the problem of converting months/years into a final number of days even more important.

The disappointing conclusion from the above is that there are no documented methodological rules for Danish arithmetic in a leap year. Only one case of converting February 29 of a leap year to February 28 of a non-leap year is described.

It would seem that the problem does not depend on the industry of application: a leap year is a leap year, even in Africa.

And here long-term insurance contracts start to spoil life – after all, during their period of validity they (the contracts) can repeatedly fall on a leap year. And this is not the worst of it, when the client (let's say) does not care about the start/end dates of the insurance periods of the contract.

Bugs queue up when needed Right break down the insurance intervals by years (financial accounting is done in terms of calendar years) and also in different systems (fronts, back, 1C, reporting, etc.). We remember that on the fronts the client does not choose the end date manually – he sets the number of months/years of the contract. That is, the system needs to somehow transform the interval into a final number, which will be recorded in the accounting system.

Here's a simple question: if a contract lasting one year began on 02/28/2023, then when will it end – 02/27/2024 or 02/28/2024?

According to the previously cited excerpt from the Federal Law, and also according to trivial programmer logic, it follows that the correct number will be 27.

Option 1 (end date February 27, 2024)

We construct a chain of periods for a 5-year contract with a start date of February 28 of a non-leap year:

That is, 2 insurance periods, affecting a leap year, are out of the logic of “Last day – penultimate day”. And the extended period (366 days) occurs only when crossing February 29.

Ugly, but monotonous. And consistent with the logic of the Federal Law.

Option 2 (end date February 28, 2024)

The same chain of periods now looks more uniform:

The same two periods are again distinguished, observing the logic of “Last day – penultimate day”. The increase in the period (366 days) occurs in a different place – the interval preceding February 29 is expanded.

As a result, a methodological question arises: what is the right way?

If you are limited by the terms of reference to unconditional adherence to the letter of the law, you are left with only the first option (February 27).

If you care about the consumer of your services and their peace of mind (an explanation to the tax office about a lost day is not very pleasant), then it is reasonable to use the second option (February 28). An additional reason to use the option is the function already implemented in DB Oracle Add_monthstaking into account similar behavior at the boundary of a leap year.

Cool! We have at least 2 possible solutions. And even a built-in calculation method. You can code with a clear conscience and relax… And catch another bug, less obvious and more tricky: what is the end date of the contract starting on 02/28/2024?

We use Add_months DB Oracle and get a surprising picture:

After 4 years, the start date of the period is shifted to 29.02.

What should you be guided by when solving such a bug, or maybe an entire feature?

This is where you really have to use your brain.

The “head-on” solution option – to use the logic “The penultimate day of the month – the penultimate – penultimate day of the month” – gives rise to a counter question. And to what depth from the end of the month date should the “pre-pre-***” principle be used? To the 27th day, the 26th … the 15th or even the first day of the month?

Or maybe the number of days in a month should be fixed and equal to 30? It seems too complicated.

Therefore, we conclude that the logic “The penultimate day of the month – the penultimate-penultimate day of the month” is not applicable here and the only exception to Danish arithmetic is “The last day – the penultimate day”.

Thus, the last 2 periods are subject to adjustment to ensure one start date of the contract:

Please note that in this particular case, to ensure the determinism of the date calculation function, it is necessary to also pass the contract start date. The ideal option is when the method centrally calculates all periods at once, instead of a recursive call for each period.

DECLARE 
  TYPE TPERIOD IS RECORD (
    pbeg DATE,
    pend DATE
  );
  vPeriod TPERIOD;
  TYPE TPERIODTABLE IS TABLE OF TPERIOD;
  
  FUNCTION CalcPeriods(pBegin IN DATE, pEnd IN DATE) RETURN TPERIODTABLE
  IS
    vResult TPERIODTABLE := TPERIODTABLE();
    vBegin  DATE := TRUNC(pBegin);
    vEnd    DATE := TRUNC(pEnd);
    vPeriod TPERIOD;
    vMonths NUMBER := GREATEST(months_between(vEnd, vBegin), 1);
    -- Необходимо корректировать 1 день в феврале високосного года
    NeedCorrectLeapYear BOOLEAN := (TO_CHAR(vBegin,'dd.mm') = '28.02') AND (TO_CHAR(vBegin,'mm') = TO_CHAR(vBegin + 1,'mm'));
  BEGIN
    FOR cMonth IN 1..vMonths
    LOOP
      IF MOD(cMonth - 1, 12) = 0 THEN
        vPeriod.pbeg := add_months(vBegin, cMonth - 1);
        vPeriod.pend := add_months(vPeriod.pbeg, 12) - 1;
        IF vPeriod.pend > pEnd THEN
          vPeriod.pend := pEnd;
        end if;
        -- Исправление ошибки разбивки периодов многолетних договоров
        IF (NeedCorrectLeapYear AND (TO_CHAR(vPeriod.pend,'dd.mm') = '28.02'))
        THEN
          vPeriod.pend := vPeriod.pend - 1;
        END IF;
        vResult.extend;
        vResult(vResult.last) := vPeriod;
      END IF;
    END LOOP;
    RETURN vResult;
  END CalcPeriods;

BEGIN
  NULL;
END;

Well, welcome to the stage the data type INTERVAL Oracle kindly crashes with ORA-01843 error on a simple query:

SQL> Select TO_DATE('29-FEB-2024','DD-MON-YYYY') + TO_YMINTERVAL('1-0') As Res From Dual;

ORA-01843: месяц неверен

This example shows that even within the same dialect of a programming language, there can be completely different logic under the hood for date functions. Not to mention unrelated languages, such as JavaScript and SQL – building a consistent date calculation will require either high competence in both languages ​​or high-quality Unit tests.

And now that's it?

Unfortunately, the direct and inverse transformation of period into date is also not always uniform.

For example, Months_Between gives different values ​​depending on the months being compared (the query is given for a part of a month; for the case of annual intervals, this feature is ignored):

SQL> Select Months_between(TO_DATE('15.02.2023','DD.MM.YYYY'), TO_DATE('01.02.2023','DD.MM.YYYY')) As Res From Dual;

       RES
----------
0,45161290

SQL> Select Months_between(TO_DATE('01.03.2023','DD.MM.YYYY'), TO_DATE('16.02.2023','DD.MM.YYYY')) As Res From Dual;

       RES
----------
0,51612903

For those who read to the end, I am attaching annual intervals that are ported to Unit tests.

An attentive reader will notice that the first and last lines of the table give different results if you do not tie them to the start date of the contract. Also, in addition to the usual tests for dates, it is advisable to add tests for continuity of periods.

With Oracle it is clear, there is a solution option, there are bugs, there are tests – you can saw your own logic. But what should those who have experienced import substitution do? And not a simple one, but on PostgreSQL.

Here we will be helped by the already familiar Extension OraFce – which has an implementation of Oracle.Add_months with all its specifics.

Which, of course, does not cancel the possibility of writing your own solution, pulling it into a micro/nano service and refactoring the entire system.

We have only looked at the tip of the iceberg of Danish arithmetic – work with annual intervals. For monthly intervals there are more nuances (remember Months_Between). And there you can’t get by with one simple crutch.

In the end, Danish arithmetic requires the performer to be meticulous in implementation and have a good knowledge of the programming language methods used to work with dates. The trick on this path is to use UT. If you forget about any of this, then in 4 years at most the green insect will wake you up again at night with falling prod )