By: Andy Novick | Updated: 2014-07-22 | Comments (1) | Related: > Testing
Problem
Testing database code has always been difficult and one of the complications is application requirements that change the behavior of code depending on the time. It might be time of day or day of week or day of month or a combination of factors that make the code do what the users need it to do. It would be nearly impossible to develop the end-of-month reports if you could only run them on the last day of the month. There are even more extreme time based issues such as what to do when a procedure starts near the end of one day, runs over midnight and completes on the next day. I wouldn't want to be up late at night so I could debug or test a procedure that depended on the transition over midnight. The QA staff that I work with agrees. As far as the QA group is concerned, all tests have to be runnable during business hours or they fail.
Solution
Over the years I've tried various approaches to testing time sensitive code. I use to go in and change the system clock of the database server to whatever I needed, but this tended to annoy the other developers using the server and the QA staff thinks that's bad form and won't cooperate. I've also just gone in and hard coded the date or time into a stored procedure for the purpose of testing and then before shipping switched back to the getdate() function. That is, unless I forget to switch it back and the code works perfectly so long as you always assume it's whatever time it was the last time that I tested.
In Test-Driven-Development (TDD) using real objects that get and save data to a database is also difficult. In TDD it has become common to abstract away the effects of the database by employing mock-objects to replace database based business objects. The mock objects pretend to be a business object and use business object logic, but the data is hard coded or drawn from a non-database source so that it's the same every time the test is run. This relieves the developer from worry about the state of the database as a test is started. This idea is a good one that can be reused for testing time sensitive database code as well.
For date and time based testing, I use what I like to call "mock-time". The mock-time starts at what ever I set it to, but then advances along with the regular system clock. In order for code to be ready for testing with mock-time application code must not use getdate(), or one of the other system time functions, but always uses the app_datetime() function that I supply. During normal processing app_datetime() returns the system time from GETDATE() but when a mock-time is set, app_datetime() returns the current mock-time. Let's see how that would work.
Let's say that I have a simple stored procedure, sales.previous_month_sales_total, that returns the total sales from the previous month along with the boundary dates of that month. For this example I'm going to create it in the Adventureworks2012 sample database that you can download from Codeplex here http://msftdbprodsamples.codeplex.com/releases/view/93587. Here's the CREATE PROC script:
CREATE PROC sales.previous_month_sales_total as
declare @current_month_start date
, @previous_month_start date
set @current_month_start = DATEFROMPARTS(Datepart(year, getdate())
,DATEPART(month, getdate())
,1)
set @previous_month_start = DATEADD(month, -1, @current_month_start)
select @previous_month_start from_date
,DATEADD(day, -1, @current_month_start) to_date
,SUM(soh.SubTotal) last_months_sales
from sales.SalesOrderHeader soh
where soh.OrderDate >= @previous_month_start
and soh.OrderDate < @current_month_start
Note that this example procedure uses the DATEFROMPARTS function that was added in SQL Server 2012. You can read about the date and time functions that were added in SQL 2012 in the tip New Date and Time Functions in SQL Server 2012.
If the procedure is executed in the sample database the results aren't very interesting:
exec sales.previous_month_sales_total GO
from_date to_date last_months_sales ---------- ---------- --------------------- 2014-05-01 2014-05-31 NULL
The problem is that there is no data for May 2014 in the database or any data for 2014 at all. To test the procedure with a time period that has data either I have to change the stored procedure temporarily and hard-code a the date that I want to test and then change it back when I'm done testing or I could create some data for May 2005, possibly by copying it from another date.
Instead, the sales.previous_month_sales_total procedure is going to be changed so that it uses app_datetime() instead of getdate() and from then on I can set the mock-time when I'm testing. Here's an ALTERed procedure that uses app_datetime(), in bold type, instead of getdate(). That's the only change that's required in application code.
ALTER PROC sales.previous_month_sales_total as
declare @current_month_start date
, @previous_month_start date
set @current_month_start = DATEFROMPARTS(Datepart(year, app_datetime())
,DATEPART(month, app_datetime())
,1)
set @previous_month_start = DATEADD(month, -1, @current_month_start)
select @previous_month_start from_date
,DATEADD(day, -1, @current_month_start) to_date
,SUM(soh.SubTotal) last_months_sales
from sales.SalesOrderHeader soh
where soh.OrderDate >= @previous_month_start
and soh.OrderDate < @current_month_start
If you execute the program without setting the mock-time you get the same results as before:
exec sales.previous_month_sales_total GO
from_date to_date last_months_sales ---------- ---------- --------------------- 2014-05-01 2014-05-31 NULL
If there were data for the current time period, it works as intended. Since I know that there is data for July 2005 I'll use mock_datetime_set to set the app_datetime() to some time in August 2005 and that will cause sales.previous_month_sales_total to return data from July, 2005. We see that here along with a query of app_datetime() and getdate().
exec mock_datetime_set '2005-08-09 13:10:10' select dbo.app_datetime(), getdate() exec sales.previous_month_sales_total GO app_datetime getdate ----------------------- ----------------------- 2005-08-10 13:43:03.923 2014-07-01 19:30:47.627 from_date to_date last_months_sales ---------- ---------- --------------------- 2005-07-01 2005-07-31 962716.7417
Calls to app_datetime() replace calls to GetDate() throughout the application code so high performance is essential to the acceptability of using the mock-time technique. If each access to app_datetime() required a database access that might slow the application, at least a bit, and it might be difficult to persuade other developers to adopt it no matter how useful it is. Of course, almost all calls to app_datetime() return getdate() without change. To minimize the overhead of checking to see if there is a mock-time in effect mock_datetime_set stores two offsets from the getdate() in the sessions CONTEXT_INFO().
CONTEXT_INFO is a section of 128 bytes of memory that is set aside for every
session. It's retrieved using the CONTEXT_INFO() function, which returns a
VARBINARY(128) value. CONTEXT_INFO is updated using the "SET CONTEXT_INFO" statement.
Here's my implementation of the mock_datetime_set procedure:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.mock_datetime_set
@mock_datetime DATETIME2(7) = NULL
/* Sets the testing time.
*
* When Who What
* 2013-06-01 Andy Novick Initial Coding
*
* example:
exec mock_datetime_set '2005-08-10 13:40:00'
select dbo.app_datetime()[app_datetime], getdate() [getdate]
******************************************************************************/
as
declare @new_context varbinary(128)
SET @new_context = CONVERT(VARBINARY(128),
COALESCE (CONVERT(CHAR(10), DATEDIFF(DAY, GETDATE(), @mock_datetime)), SPACE(10))
+ COALESCE (CONVERT(CHAR(10), DATEDIFF(millisecond
, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), @mock_datetime)
, GETDATE()
)
, @mock_datetime)
)
, SPACE(10))
)
SET context_info @new_context
GO
The procedure converts the current getdate() into two numbers: the number of days between the getdate() and the mock date-time, and the milliseconds offset from getdate(). These offsets are converted to characters strings and saved in context_info. The reason for using the offsets is that I want app_datetime() to advance with the time. Once the offsets are stored, they're used in the app_datetime() function shown here:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION app_datetime (
) RETURNS datetime
/* Return the application time, which is usually getdate but during testing may be
* changed to the time being tested.
*
* 2014-06-01 Andy Novick Initial Coding
*
select dbo.app_datetime() [app_datetime], getdate() [getdate]
**************************************************************************************/
AS BEGIN
declare @context_info varbinary(128) = context_info()
, @date_offset_str CHAR(10)
, @millisecond_offset_str CHAR(10)
if @context_info is null return getdate()
SELECT @date_offset_str = SUBSTRING(convert(varchar(128), @context_info), 1, 10)
, @millisecond_offset_str = SUBSTRING(convert(varchar(128), @context_info), 11, 10)
RETURN CASE WHEN @context_info IS NOT NULL
AND COALESCE(@date_offset_str, '') != ''
AND 1=ISNUMERIC(@date_offset_str)
AND 1=ISNUMERIC(@millisecond_offset_str)
THEN DATEADD (MILLISECOND, CONVERT(INT, @millisecond_offset_str)
, DATEADD(DAY
, CONVERT(INT, @date_offset_str)
, GETDATE())
)
ELSE GETDATE()
END
END
GO
Access to context_info() is very fast and all the function has to do is return getdate() when the mock-time isn't set. However fast that is, any scalar user-defined function that is called a large number of times risks slowing the query that invokes it. Be careful of using app_datetime() in the select list or where clause of a query that handles more than a couple thousand rows. Instead, save app_datetime() to a variable and use the variable in the query.
I find context_info a very useful place to store the mock-time as well as flags that I use to control logging and debugging code throughout my applications. However, it's a limited resource with only 128 bytes so must be used sparingly. Also, all developers working on a database have to agree how it's going to be used.
Next Steps
- Decide if your testing efforts could benefit from being able to set an arbitrary mock time.
- If so add mock_datetime_set and app_datetime() to your database and give it a try.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-07-22
