Time might be a dimension your tests do not cover

Here's a situation I've needed to solve more than once, and I suspect I'm not alone.

An invoicing system calculates VAT (value-added tax). Then, the rate changes; say from 24% to 25.5% on the first of January. There's a business rule that when the rate has recently changed, the printed invoice carries an extra note line explaining the change. Straightforward enough, and the automated tests pass green.

Eleven months later, a customer calls during their internal audit. They've lost an invoice from last March and need a copy. You reprint it. But, the reprint uses today's VAT rate, and the note line logic runs against today's date. The reprint doesn't match the original. Nobody caught it, because nobody tested the reprint at a date other than the day the test suite ran.

That's the shape of the problem. Time-dependent logic tends to be tested at exactly one point in time: whenever the tests happen to run.

Luckily, the Microsoft stack gives you real tools for both halves of this: SQL Server (data part) and .NET/C# (logic part) both have solutions for exactly this. Earlier, this wasn't the case.

For the data part, SQL Server has system-versioned temporal tables:

CREATE TABLE TaxConfiguration
(
    Id           INT PRIMARY KEY,
    VatPercentage DECIMAL(5,2) NOT NULL,
    ValidFrom    DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo      DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

If you define a table as above, you can then ask what the world looked like at a moment:

SELECT VatPercentage
FROM TaxConfiguration
FOR SYSTEM_TIME AS OF '2025-03-15';

That is the data part. For the coding logic part, .NET 8 gave us TimeProvider, which finally makes the system clock injectable without hand-rolling an IClock abstraction:

public class InvoicePrinter(TimeProvider timeProvider)
{
    public Invoice Print(Order order)
    {
        var now = timeProvider.GetUtcNow();
        // ...logic that depends on "now"
    }
}

...and in the test:

FakeTimeProvider fakeTime = new(new DateTimeOffset(2025, 3, 15, 0, 0, 0, TimeSpan.Zero));
InvoicePrinter printer = new(fakeTime);

Both of these are genuinely good. Use them if you can.

You must remember to change time both in logic and in the data

In the above C# code, I set the application's clock to March 2025. And my queries still return today's data.

The TimeProvider moved my code to March. It did nothing whatsoever to my database. To move the data too, I have to thread the date through every query by hand:

var vat = await db.TaxConfiguration
    .TemporalAsOf(asOfDate)  // remember this on every single query
    .Select(t => t.VatPercentage)
    .FirstAsync();

Every query, every table, every join. Not fun. Forget one and you've silently mixed March's VAT rate with today's customer address, and your test passes while asserting something that never existed. (For more information about Entity Framework's support for temporal tables, see here.)

So, the two halves don't talk to each other. The clock abstraction knows what time it is and the database doesn't. I'm the integration layer between them, by hand, in every query. That's the part that's tedious enough that in practice, most teams just... don't. They test at "now" and hope.

Here is what I do to solve these issues, for what it's worth:

• Thread a single "as of" date through the operation, not the request. One parameter, set once at the top of the operation, passed down. Never read the clock below that level. If a method needs to know what time it is, it takes it as an argument.
• Make the date-boundary cases explicit test names. VatNoteLine_Appears_When RateChangedWithinLast30Days and VatNoteLine_Absent_WhenRateChangeIsOlder. If you can't name the boundary, you haven't found one yet.
• Test both sides of every boundary. The bug is never in the middle of the range, it's one day either side of the change.
• Treat "reprint an old document" as a first-class feature with its own tests, not as a variation of "print". They're different operations with different correctness conditions, and the second one is the one that gets audited.

None of this is clever. It's just discipline, applied in enough places to make it work.

The open question

Here's what I keep coming back to. The database already knows the history. SQL Server's temporal tables have the whole record sitting right there. The engine has all the information needed to answer "what did this row look like in March"; and it will answer it, once, per query, if I remember to ask it every single time.

So why is it my job to remember, on every query, that I'm currently pretending to be in March?

Why can't I set the temporal context once, for a connection, a transaction, a scope, and have every read inside it resolve as of that moment, automatically, consistently, the way TimeProvider does for my code? Why isn't there an ambient "as of" the way there's an ambient clock?

I don't think this is a hard question technically. The engine already stores the versions and already knows how to resolve them. It doesn't have a notion of "stay there for a while".

Maybe there's a good reason I'm not seeing. Maybe it's a transaction-isolation argument, or a query-plan-caching one. But from where I sit writing the same .TemporalAsOf(date) on the fourteenth query in a method and knowing I'll forget it on the fifteenth, feels like something the database could do for me.

If your database can do this, I'd genuinely like to hear about it.