How to fake time in a Postgres database

First things first, I am writing this despite my general opposition to faking time. Typically, when invoking an API in a unit or integration test, it’s preferable to pass time as a value as opposed to mutating a global clock. And if you’re writing full-stack tests, then crafting data & scripts that pass regardless of time offers some validation that the app behaves the same way every day of the year.

However.

Time is so important to many application domains that detangling temporal dependencies from a feature’s business logic is sometimes infeasible. For many such apps, any non-trivial end-to-end test would be very difficult to implement without controlling for the current time.

I’m writing this after a failed weekend experiment to build durable test data for my KameSame app (which I discussed in “The Selfish Programmer”). The app implements a spaced repetition system for learning Japanese that orchestrates a careful sequence of timers for each word being studied—often numbering in the thousands for a typical user. Additionally, the entire UI revolves around presenting both individual and cumulative statuses of these timers relative to the current time such that the app may appear completely differently depending on when a user logs into it. Finally, many of the app’s most complex features work by modifying this relationship with time, like deferring reviews during a vacation or spreading out scheduled reviews to combat overwhelm.

Language-level libraries like timecop provide utilities for freezing and traveling in time by augmenting the values returned by the standard library (Time.now or Date.today in Ruby’s case). These libraries are generally sophisticated and reliable until your application needs to communicate with the outside world, like an HTTP service or a database—a somewhat common requirement of modern applications. If the application thinks it’s the year 2042 and the database thinks it’s still 2022, hijinks will ensue in rough proportion to the amount of logic that’s been implemented in the database.

In reaction, some people might point to this state of affairs as a reason to never rely on the database’s sense of the current time, and instead advocate diligently passing time as a parameter with every query (as opposed to ever using SQL functions like now() in order to preserve testability. This line of thinking isn’t without merit, but in my case, following it would necessitate unwinding years of performance optimizations achieved by moving data-intensive logic into SQL views and functions. Testable design patterns have value, but when they come at the expense of runtime performance, they’re (appropriately) a tough sell.

How to fake database time

Okay, let’s get to work! As you read, feel free to reference this companion example app that implements everything discussed in this blog post.

Here’s all we need to fake time on a mechanical level:

1. A place to store a desired time offset relative to the system clock

2. A custom function that returns a fake time by adding the real time with that stored offset

3. Invoking that new database function everywhere we access the current time

4. A method to fake both our application and the database’s current time in lock-step with one another

Storing a time offset

Most of my apps end up with something like a system_configurations table with a single row and a corresponding singleton Active Record model, in which I’ll store the state of deployment-wide configuration and status. (For example, I might store a last_updated_japanese_dictionary_at timestamp in this table).

The migration for such a configuration table might look like:

class CreateSystemConfiguration < ActiveRecord::Migration[7.0]
  def change
    create_table :system_configurations do |t|
      t.bigint :global_time_offset_seconds, null: false, default: 0

      t.timestamps
    end
  end
end

Now all we need is a singleton SystemConfiguration model:

class SystemConfiguration < ApplicationRecord
  def self.instance
    if (system = first)
      system
    else
      SystemConfiguration.create!
    end
  end

  def reset_global_time_offset_seconds!
    update!(global_time_offset_seconds: 0)
  end
end

So long as it’s always accessed with SystemConfiguration.instance, each deployed environment will have at most one persisted system configuration at a time. (We can also enforce this with something like this insert trigger.) Effectively, this means we can set a single global_time_offset_seconds value and rely on it across the application.

You might notice that I settled on an offset as a bigint of seconds. I did this because my attempts to get fancy and make use of Postgres’s interval type proved too fussy; resulting in a lossy conversion of the difference between two times into a duration in order to construct an ISO8601 string that wouldn’t raise out-of-range errors (which the Active Record PG adapter often will).

Creating a wrapper function for now()

Now that the offset can be stored, we need a SQL function to add it to the real time returned by now().

Here’s a migration defining such a function, named nowish():

class CreateNowishFunction < ActiveRecord::Migration[7.0]
  def up
    # Written to mimic the shape of `pg_catalog.now()':
    #
    # CREATE OR REPLACE FUNCTION pg_catalog.now()
    #  RETURNS timestamp with time zone
    #  LANGUAGE internal
    #  STABLE PARALLEL SAFE STRICT
    # AS $function$now$function$
    #
    execute <<~SQL
      CREATE OR REPLACE FUNCTION public.nowish()
      RETURNS timestamp with time zone
      AS
      $$
      BEGIN
      RETURN pg_catalog.now() + (select global_time_offset_seconds
        from public.system_configurations
        limit 1
      ) * interval '1 second';
      END;
      $$
      LANGUAGE plpgsql STABLE PARALLEL SAFE STRICT;
    SQL
  end

  def down
    execute "drop function public.nowish()"
  end
end

Note that calling nowish() will be slightly more costly than calling now(), but the fact that it’s a STABLE function that selects at most one column of one row of a singleton table limits that cost somewhat. One might explore mitigating this in several ways: ❶ defining nowish() as an alias of now() in production, ❷ shadowing now() via clever use of schema search paths as in this branch of our example app, or ❸ relying on Postgres’s aforementioned configuration parameter feature instead of storing the offset in a table.

Replace all references from now() to nowish()

Next up: changing every reference to the current time in our schema to instead call our new nowish() function.

The difficulty of this will depend entirely on how many places calls now() and its myriad case-insensitive synonyms and related functions like clock_timestamp and age(timestamp). Be prepared to grep!

In the case of my KameSame, this required find-and-replacing about 40 references over a few straightforward migrations. It honestly was nowhere near as invasive as I thought it’d be. Many of those migrations boiled down to changing a column default like this:

class ChangePetsBornAtDefault < ActiveRecord::Migration[7.0]
  def change
    change_column_default :pets, :born_at,
      from: -> { "now()" }, to: -> { "nowish()" }
  end
end

That said, this is hardly a trivial commitment. For this to work at all, every reference to the current time needs to run through a function that will return the intended fake time. This level of coordination may be a tall order for large teams, especially in the absence of a linter or commit hook to enforce compliance.

Create an API for traveling through time

Given all the above, we can finally create a class or function in our application that will travel to a desired point in time, both for the programming language and for the database.

Here’s a Ruby class that brings this all together:

class TravelsInTime
  def call(destination_time)
    Timecop.return
    set_pg_time!(destination_time)
    set_ruby_time!(destination_time)
  end

  private

  def set_pg_time!(destination_time)
    SystemConfiguration.instance.update!(
      global_time_offset_seconds: destination_time - Time.zone.now
    )
  end

  def set_ruby_time!(destination_time)
    Timecop.travel(destination_time)
  end
end

Now manipulating time for both Ruby and Postgres is as simple as:

TravelsInTime.new.call(1.year.from_now)

And, boom! You missed your next birthday.

Of course, you’re welcome to play with this working example Rails application to familiarize yourself with the approach before attempting to implement it in your own applications.

When distorting reality, mindset matters

Most developers follow a reasonable but simplistic intuition about mocking: real things are better than fake things, so fakeness should be minimized to the extent possible. This perspective isn’t wrong, but it often discounts how incomprehensibly complex reality is. No test is going to perfectly simulate real-world usage, but the more “real” we make a test, the more variability it will be exposed to and the less confident we’ll be that we know what it means when the test fails.

Instead of weighing these concerns as budgeting concessions against reality, a prompt I’ve found more productive is to instead envision tests as scientific experiments, where any extraneous factors that can be fixed in place will become part of the test’s experimental control. The more we’re able to control, the clearer the test’s intentions will be to the reader and the greater our confidence that its hypothesis is confirmed when the test passes.

Through the lens of experimental control, I can make more informed decisions about what to fake and when. If I’m testing a pure function that takes a time and returns a different time, the value of the system clock wouldn’t even occur to me as being relevant to the experimental design of the test. But what if I’m writing an end-to-end test of a time-keeping app that is designed to behave dramatically differently on Thursdays than on Fridays (say, when timesheets are due)?

Giving into an impulse to minimize fakeness will sometimes result in a test that—while superficially less invasive—is actually less resilient to implementation changes and less clearly expresses its intent by becoming cluttered with test-scoped logic needed to exercise the desired behavior. Being unabashed about seizing control of the clock to whatever extent it’s accessed by the system could allow the same test to survive significant refactors and be authored plainly in terms of the observable behavior expected on a given day of the week.

In software, there are multiple right answers to almost any task, but each one comes with its own trade-offs. If those trade-offs lead you to ever faking time in your database, I hope this approach will serve you well!