Home » Blog » Testing Time – Three Ways to Avoid Fragile Tests

Testing Time – Three Ways to Avoid Fragile Tests

2015-01-07 - Fragile Tests, General, Series, TDD, tSQLt

In the first post of this series about fragile tests, we talked about how fragile tests can be a huge problem, undermining your unit testing efforts. In this second post of the series, I am looking at a special type of fragile tests: Tests that cover functionality that is dependent on the current time or date.

Time Dependent Tests

A few weeks ago Jim asked a question on the tSQLt mailing list (tsqlt@googlegroups.com) about how to best test functionality that is dependent on one of the built-in time functions like SYSUTCDATETIME(). I did, at the time, reply with an answer that got Jim unstuck, but I think it is worth going into a little more detail on this topic.

Jim was working on a procedure involved in application password resets. One of the business rules was to set an expiration time of DATEADD(MINUTE,15,SYSUTCDATETIME()) on any temporary password that was generated in this process.

Sporadic Test Failures

When you try to test this functionality, you should really test that the value written is exactly 15 minutes from now. However, test runtimes vary from execution to execution, making this impossible. Instead of testing the time exactly, you could allow for an uncertainty factor, for example 15 minutes from now plus/minus 50 milliseconds. Such a test could look like this:

[sql] CREATE PROCEDURE TimeDependentTests.[test example with 50ms fuzzy interval] AS
BEGIN
EXEC tSQLt.FakeTable @TableName = 'Person.TempPasswords';

EXEC Person.CreateTemporaryPassword @PersonId = 2346;

DECLARE @ExpirationTimeDifference INT;
SELECT @ExpirationTimeDifference = DATEDIFF(MILLISECOND,SYSUTCDATETIME(),ExpirationDTime)
FROM Person.TempPasswords;

IF( @ExpirationTimeDifference < 15*60*1000-50
OR @ExpirationTimeDifference > 15*60*1000-50
OR @ExpirationTimeDifference IS NULL
)
BEGIN
EXEC tSQLt.Fail 'Wrong ExpirationTime set.';
END;

END;
[/sql]

However, for different reasons execution times of the same test can vary by a lot. This is particularly common on a development machine with a local SQL Server instance, but can happen on larger development or test servers too. Therefore, you might still see sporadic failures. You could now arbitrarily expand the interval, but I would not recommend going that way. The more uncertainty you allow, the more likely it is for an actual problem to slip through.

Possible Solutions

There are several ways to handle time dependencies in your code (and tests). The three I most often use are:

Parameterizing
Wrapping
Bracketing

Let's look at them one by one.

Parameterizing

Parameterizing time means, instead of calling a system function like SYSUTCDATETIME() to get the current date and time within a procedure, require the current date and time to be passed in as a parameter. This has two big advantages. First, it makes testing a lot simpler, because now you can just pass in a known fixed timestamp in your tests:

[sql] CREATE PROCEDURE TimeDependentTests.[test example with parameterized date and time] AS
BEGIN
EXEC tSQLt.FakeTable @TableName = 'Person.TempPasswords';

EXEC Person.CreateTemporaryPassword
@PersonId = 2346,
@NowUTC = '2015-01-07T09:57:15.1234567';

SELECT ExpirationDTime
INTO #Actual
FROM Person.TempPasswords;

SELECT TOP(0) *
INTO #Expected
FROM #Actual;

INSERT INTO #Expected
VALUES('2015-01-07T10:12:15.1234567');

EXEC tSQLt.AssertEqualsTable '#Expected','#Actual';
END;
[/sql]

Besides of not being fragile, the test is immediately more readable. Also, if it fails it gives clearer information about what went wrong. Additionally, you can be sure that a failure was actually caused by faulty code and not some one-time processing fluke.

The second advantage is not quite as obvious. I am often called to clients after they had a temporary problem, causing some important nightly/weekly/monthly process not to run. Now that process needs to be "caught up". However, the processing is based on the current date or time and cannot just be executed for say yesterday. Now clients are often contemplating an approach like this: Take server offline. Change server time. Rerun the process. Change server time back. Bring server back online.

This is an enormous effort. It also requires a downtime, which is often not an option. After that plan is ruled out, they start taking the code from the original process and write a new version that can handle this specific case. This is usually a high-pressure situation and causes all kinds of shortcuts to be taken. The thing that usually goes first is adequate testing. You probably can imagine what kind of disaster this leads to quite often.

Now imagine how simple it would be to rerun the process for yesterday's date if you could just pass that date in as a parameter. Granted there might be other complications, but at least the time component would not be a hassle anymore.

The above applies to procedures and functions that are not part of a regular process too. In fact, I add that @NowUTC parameter to all procedures and functions that are in any way dependent on the current time.

Wrapping

Sometimes you cannot use the "parameterizing" pattern and you have to get the current date or time in your code directly. For example, if you use parameterization, you might want to make that parameter optional. Or, you might want to capture the current time within a loop. In the case of the @NowUTC parameter being optional, it would have to be valued within the procedure, for example like this:

[sql] CREATE PROCEDURE Person.CreateTemporaryPassword
@PersonId INT,
@NowUTC DATETIME2 = NULL
AS
BEGIN
SET @NowUTC = COALESCE(@NowUTC,SYSUTCDATETIME());

-- [other functionality here] END
[/sql]

Instead of directly calling SYSDATETIME() here, I recommend that call to be wrapped in a function:

[sql] CREATE FUNCTION Util.CurrentDateTime()
RETURNS TABLE
AS
RETURN SELECT SYSUTCDATETIME() AS NowUTC;
[/sql]

[sql] CREATE PROCEDURE Person.CreateTemporaryPassword
@PersonId INT,
@NowUTC DATETIME2 = NULL
AS
BEGIN
SELECT @NowUTC = COALESCE(@NowUTC,N.NowUTC)
FROM Util.CurrentDateTime() AS N;

-- [other functionality here] END
[/sql]

In a test we can no use tSQLt.FakeFunction to replace that function with one that returns a hardcoded value:

[sql] CREATE FUNCTION TimeDependentTests.[return 2015-01-07T09:57:15.1234567]()
RETURNS TABLE
AS
RETURN SELECT CAST('2015-01-07T09:57:15.1234567' AS DATETIME2) AS NowUTC;
[/sql]

[sql] CREATE PROCEDURE TimeDependentTests.[test example with wrapped SYSDATETIME()] AS
BEGIN
EXEC tSQLt.FakeTable @TableName = 'Person.TempPasswords';
EXEC tSQLt.FakeFunction
@FunctionName = 'Util.CurrentDateTime',
@FakeFunctionName = 'TimeDependentTests.[return 2015-01-07T09:57:15.1234567]';

EXEC Person.CreateTemporaryPassword @PersonId = 2346;

SELECT ExpirationDTime
INTO #Actual
FROM Person.TempPasswords;

SELECT TOP(0) *
INTO #Expected
FROM #Actual;

INSERT INTO #Expected
VALUES('2015-01-07T10:12:15.1234567');

EXEC tSQLt.AssertEqualsTable '#Expected','#Actual';
END;
[/sql]

This type of wrapping is a very common technique in object-oriented design. In SQL Server, we have the complication that we have to be concerned with performance at all times. However, the impact of wrapping a system function like SYSDATETIME into an inline table valued function is very small. While it is certainly measurable, in most real life scenarios is it negligible.

Bracketing

There are a few cases where you cannot take either approach discussed before. For example, you have to test the Util.CurrentDatetime function at some point. Or, you might be in a situation where you have to test some existing code that for one or the other reason cannot be changed at the current time. In that case, you can use bracketing.

Bracketing is a testing technique that takes a timestamp immediately before and after an event like a procedure call. Anything that happened in that procedure call happened between those two time stamps. That means that any call to e.g. SYSUTCDATETIME() within that procedure must have also returned a time between those two recorded timestamps. That however we can assert in a test:

[sql] CREATE PROCEDURE TimeDependentTests.[test example with bracketing] AS
BEGIN
DECLARE @actual DATETIME2;
DECLARE @after DATETIME2;
DECLARE @before DATETIME2;

SET @before = SYSUTCDATETIME();

SELECT @actual = N.NowUTC
FROM Util.CurrentDateTime() AS N;

SET @after = SYSUTCDATETIME();

IF(@actual < @before OR @actual > @after OR @actual IS NULL)
BEGIN
DECLARE @msg NVARCHAR(MAX) =
'Expected:'+
CONVERT(NVARCHAR(MAX),@before,121)+
' <= '+
ISNULL(CONVERT(NVARCHAR(MAX),@actual,121),'!NULL!')+
' <= '+
CONVERT(NVARCHAR(MAX),@after,121);
EXEC tSQLt.Fail @msg;
END;

END;
[/sql]

This test pattern is robust and it will not lead to fragile tests. However, you still have a certain uncertainty factor, particularly if the procedure you are testing is longer. In addition, this type of test tends to become very convoluted quickly. That is particularly the case, if you cannot directly access the used DATETIME2 value and have to use indirect measures instead. Therefore, I recommend using this pattern only for very simple functions and procedures. Functions that just wrap a system call are well fitting candidates for this unit testing approach.

Final Thoughts

There are several ways to deal with time dependencies in unit tests. In this article, I demonstrated three of them that together give you a robust solution for almost any time dependent unit test situation. Whenever possible, add a parameter to any time dependent module to allow the current time or date to be passed in. That makes life easier in many situations, not only during unit test creation.

You might have noticed me using SYSUTCDATETIME() instead of SYSDATETIME(). UTC stands for Coordinated Universal Time (French: temps universel coordonné). UTC time is the same across the entire globe and not dependent on the time zone you are in. In fact, it is the only time that is affected neither by time zones nor by daylight savings. Both, time zone calculations and daylight savings can cause complexities and ambiguities that can be easily avoided by storing every time as UTC. Believe me, it is a lot easier to convert a stored UTC time into the correct display time zone at runtime than it is to fix corrupt data in the database because a developer did not anticipate that there is one day a year on which 2:17 AM happens twice.

You also might have thought my timestamps look funny. That is another ambiguity-avoiding tactic. The format YYYY-MM-DDTHH:MM:SS.FFFFFFF with a "T" between the date and the time is the only format that is interpreted the same way by SQL Server, independent of settings like the local language. It also is universally understood by people around the globe whereas "05/03/2015 9:15" is ambiguous to many.

Using these two pieces of advice, you can save yourself the hassle of test cases failing because your colleague in e.g. India used a different time zone or language setting.