Faking Date/Time in a SQL Server Docker Container for Integration Testing

In software development, time-dependent tests often present challenges, particularly when working with databases like SQL Server. Integration testing may require specific date/time contexts to simulate real-world scenarios, such as validating date-based calculations, time-bound data processing, or auditing behavior.

So, we just change the container’s system date/time to what we need and we’re good, right? Well, it turns out that it’s not an easy thing to do.

Why is this Complicated?

Changing a Docker container’s system date/time is challenging primarily because containers share the host machine’s kernel. Unlike virtual machines—which each run their own operating system kernel and can independently manage their clocks—Docker containers are more lightweight and simply isolate processes using the same kernel as the host.

Here are a few specific reasons why this becomes complicated:

1. Shared Kernel, Shared Clock: Since containers don’t have their own kernel, they inherit the host’s system time. Attempting to modify it inside a container usually leads to a system-wide change or is immediately overridden by the host’s real clock.
2. Lack of Namespace Support: While Linux supports various namespaces (for process IDs, networking, user IDs, etc.), full “time namespace” support—where each container can have its own independent clock—remains limited or still in development for mainstream container runtimes.
3. Synchronization Mechanics: Even if you set up a fake NTP server or other “time spoofing” within a container, the container can recognize the discrepancy when the host clock remains different, and it will revert back to the real time (or fail to override it entirely).
4. Application-Specific Incompatibilities: Some tools (like libfaketime) intercept time syscalls at the library level, but they can break applications that rely on custom memory allocators or low-level timing functions—such as Microsoft SQL Server on Linux. This makes it even harder to fake time in certain containers.

Enter dateoffset

Rafael Batiati’s dateoffset tackles the memory allocation issue by bypassing dlsym and calling the Linux clock_gettime syscall directly. Because it doesn’t rely on intercepting time functions through an intermediary that allocates memory, it avoids the conflict that brings down SQL Server.

Basic Workflow

1. Set LD_PRELOAD: You load dateoffset.so into your container or process with the LD_PRELOAD environment variable.
2. Define DATE_OFFSET: This environment variable specifies the fake “start date” in epoch format. Any subsequent calls to clock_gettime in that container or process will appear to happen at your chosen date/time.
3. Run Your Application: Once LD_PRELOAD and DATE_OFFSET are set, your SQL Server process (or any other time-dependent service) sees the adjusted system time.

Using dateoffset in a SQL Server Container

Even with this library and knowledge, implementing this in a SQL Server container is non-trivial. But, Rafael has done us all a solid and setup another repository that’s specific to SQL Server Testing: https://github.com/batiati/mssql-testing. In addition to helping set a fake date/time with ease, his docker image also features a handful of other quality of life improvements over the plain vanilla SQL Server images provided by Microsoft:

• Allows you to specify .mdf/.ldf files in a JSON format to easily bootstrap databases
• Enabling CLR functionality in SQL Server
• Allowing you specify a weak password, if necessary
• Configure the option to auto close the database if there are no active connections
• Configure the maximum memory that SQL Server can use

Take it for a Spin

Grab a copy of the image from Docker hub:

docker pull batiati/mssql-testing

Run the container:

docker run -d \
-p 1433:1433 \
-v /volume:/volume \
-e ACCEPT_EULA=Y \
-e SA_PASSWORD="St5rOng%Pass@rD_" \
-e ATTACH_PATH="/volume/attach.json" \
-e ENABLE_CLR=Y \
-e SA_NO_POLICY_PASSWORD="1234" \
-e AUTO_CLOSE=ON \
-e MAX_MEMORY=1024
-e RUN_AS_DATE="2019-11-25"
-e TZ=America/Sao_Paulo
batiati/mssql-testing

Or reference it in a docker-compose file:

services:
  sql-server:
    image: batiati/mssql-testing
    container_name: sql-server
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=yourStrong(!)Password
      - SA_NO_POLICY_PASSWORD="1234"
      - RUN_AS_DATE=2019-11-25

Test and Verify

Connect to your SQL Server container via SSMS or Azure Data Studio or another utility and try:

SELECT GETDATE();
SELECT UTCDATE();
SELECT SYSDATETIME();
SELECT SYSDATETIMEOFFSET();

They should all return the mock date that you started the container with!

Closing Remarks

Faking the date/time in a SQL Server Docker container offers a flexible way to execute time-dependent integration tests reliably. A great many thanks to Rafael Batiati, for his great work on this. Thank you for sharing it with the development community!