Unit Test failures with date/timestamp logic

We have a number of utility packages that deal with dates and timestamps and we create automated unit tests for them using utPLSQL which is an excellent unit test framework for PL/SQL. These unit tests are executed by our automated scripts whenever we merge changes into our git repository; this gives us early warning if something we’ve changed may have caused regressions.

An issue I’ve encountered just a few times has been that some of these unit tests, extremely rarely, fail with “off-by-one” errors; e.g. a function was expected to return a string like “2 days ago” but instead it returned “3 days ago”. In another test, a function that accepted a string like “Today” returned the date “7 July 2025” but the unit test expected it to return “8 July 2025”.

declare
    c_now constant timestamp with local time zone := localtimestamp;
begin
    ut.expect(
        util_report.get_since( c_now - numtodsinterval(2, 'day') )
    ).to_equal( '2 days ago' );
end;
/

FAILURE
  Actual: '3 days ago' (varchar2) was expected to equal: '2 days ago' (varchar2)

These test failures were always rare, seemingly unpredictable, apparently unrelated to any recent changes, and rerunning the same test would never reproduce the failure. In fact, the automated unit test system would succeed the next time it ran, with no intervention. Also, each time it would be a different unit test that failed; sometimes two extremely similar unit tests (e.g. ones that were “opposite” of each other) would have conflicting success/failure results.

Diagnosis

It was not difficult to guess what was causing these test failures due to the nature of the failure: the expected value was always 1 unit earlier or 1 unit later than the actual value returned; where the “unit” here might be a day, a month, or a second, depending on what was being tested and the nature of the test. The unit test calculates the expected value based on the current date/time and either stores this value somewhere, or passes it directly to the relevant UT procedure; the unit test executes the test, which itself internally also retrieves the current date/time. Most of the time, the execution time is very quick and the two dates or timestamps are either identical or at most a fraction of a second apart; when a feature being tested only needs them to be the same day, or month, this will “always” be true.

Except, of course, it will not always be true; if the unit test happens to be executed right about the time the clock ticks over from one day to the next, or one month to the next, the two “TODAYs” will still be very close to each other but their day or month will be different. This difference would cause various unit tests to fail.

How did we resolve this issue? My first approach was a bit of a hack; for each unit test, instead of saying “I expect the result to be X”, I modified each unit test to say “I expect the result to be X or X+1” or something like that. It would compare the values, allow the test to succeed if it was off by one, and call it a day.

Unfortunately trying to work out what “X+1” really means in every scenario became rather complex for some of our functions; sometimes the direction might be negative, and might be a different unit than expected. Ultimately the hack was unsatisfactory because it introduced an uncertainty that a real bug might still pass the unit test, if the bug itself introduced an off-by-one error.

Solution

Instead, what we needed was for the unit tests to be run in an artificial environment where today’s date and time are fixed and known, so that our expected values can be predictable.

Now if we were using SYSDATE throughout our code to get the current date/time, we might consider using the Oracle database’s FIXED_DATE feature to set the return value of SYSDATE to a known value. In our case, however, we use CURRENT_DATE and LOCALTIMESTAMP (and sometimes SYSTIMESTAMP) throughout our codebase, and these internal functions ignore FIXED_DATE.

The approach we took was to replace the critical calls to CURRENT_DATE, LOCALTIMESTAMP and SYSTIMESTAMP with our own wrapper functions. These are only needed in the specific PL/SQL packages that need this level of unit testing and where the unit tests actually need to test what they return with respect to today’s date and time, so we didn’t replace all the references throughout our codebase.

Our wrapper functions use the context value that the utPLSQL framework sets to determine whether the current code is being executed in a unit test (including within any setup/teardown procedures in a unit test package); if the context value is not set, we return the real date/time as normal.

CREATE OR REPLACE PACKAGE util IS

    -------------------------------------------------------------------------
    -- Wrapper for current_date to allow unit testing
    -------------------------------------------------------------------------
    function current_date return date;

    -------------------------------------------------------------------------
    -- Wrapper for localtimestamp to allow unit testing
    -------------------------------------------------------------------------
    function localtimestamp return timestamp;

    -------------------------------------------------------------------------
    -- Wrapper for systimestamp to allow unit testing
    -------------------------------------------------------------------------
    function systimestamp return timestamp with time zone;

END util;
/

CREATE OR REPLACE PACKAGE BODY util IS

    c_ut_owner       constant varchar2(30) := 'UT';
    c_test_timestamp constant timestamp := timestamp'2025-01-01 12:00:00.000';

    -------------------------------------------------------------------------
    -- Wrapper for current_date to allow unit testing
    -------------------------------------------------------------------------
    function current_date return date is
    begin
        -- if we're in a utPLSQL unit test, return a static known value
        if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
            return cast(c_test_timestamp as date);
        end if;
        return standard.current_date;
    end current_date;

    -------------------------------------------------------------------------
    -- Wrapper for localtimestamp to allow unit testing
    -------------------------------------------------------------------------
    function localtimestamp return timestamp is
    begin
        -- if we're in a utPLSQL unit test, return a static known value
        if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
            return c_test_timestamp;
        end if;
        return standard.localtimestamp;
    end localtimestamp;

    -------------------------------------------------------------------------
    -- Wrapper for systimestamp to allow unit testing
    -------------------------------------------------------------------------
    function systimestamp return timestamp with time zone is
    begin
        -- if we're in a utPLSQL unit test, return a static known value
        if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
            return c_test_timestamp at time zone 'GMT';
        end if;
        return standard.systimestamp;
    end systimestamp;

END util;
/

In the PL/SQL packages and in the unit tests for those packages, we just needed to replace all references to current_date, localtimestamp, or systimestamp with util.current_date, util.localtimestamp, and util.systimestamp. This means the unit tests will always get the same value from these functions regardless of what today’s date and time actually is.

Note that this approach did not require adding any extra setup or teardown code to the unit tests, so it was quite simple to implement.

If you are using utPLSQL and are considering using this approach, remember to set c_ut_owner to the schema owner for where you have installed utPLSQL (UT, in the sample code above).

This approach is, however, not universal; in other packages with unit tests that actually need to test the amount of time that passes between two events, we would not call these utility functions; those unit tests don’t depend on a particular date or time, but they do depend on the dates/times being different. Therefore we must take care to consider in each case whether we should use these wrapper functions.

Alternative Approaches

If our situation was more complex, and we needed some unit tests to execute with the “real” dates and times, we would use a slightly different approach; i.e. allow the unit test setup code to enable or disable this behaviour, and/or instead of using a single constant value, allow unit tests to set any particular date/time to suit the specific requirements of the unit test (e.g. by setting a private global in the UTIL package).