Set Postgres' current_timestamp to constant for JUnit test?

Question

I'm using JUnit to test my repositories that access a Postgres database.

All my test data was set manually so I know exactly what values I expect to get in a result. I have troubles testing statements that use current_timestamp though, ones like select * from phone_number where current_timestamp <= expires;

The trouble is that my test data is constant, but current_timestamp advances in time with every test execution.

Is there a way to make current_timestamp return a constant value?

Of course this would be one of several options, I was three more solutions:

• Define a base date (i.e., an epoch, something like base_date timestamp default current_time) in my test data. All other data is defined as an interval after that epoch data (e.g. as base_date + interval '1 day'). Knowing the rough runtime of my tests, I could set expires timestamps that definitely won't be exceeded during my tests (e.g. hours after after base_date when the runtime is seconds). Having variable values in my test data makes it harder to check though.
• I could stop using current_timestamp and use an additional timestamp argument in all my statements that in normal use (in Java) would be something like System.currentTimeMillis() or new java.util.Date() (in any case, 'now'), and in my tests I could pass constant dates. I'm not a fan of this solution though because a) it introduces changes that are not needed in the normal usage (outside of tests) b) additional parameters are a source of additional errors.
• Define a stored procedure that returns current_timestamp unless a certain value is present which indicates that 'testing mode' is on and a constant should be returned instead. This might actually work best, but might not be necessary if there's an easy way in Postgres to control what current_timestamp returns.

Answer 1

Instead of trying to do something in the database itself, or refactoring your code just to allow for particular tests, you could wrap your database calls in your unit tests, replacing current_timestamp in the query strings with a typed literal value. For example:

public ResultSet executeQuery(String sql) {
    return wrapped.executeQuery(
        sql.replaceAll("\\bcurrent_timestamp\\b",
                       "'20160425 10:12'::timestamptz"));
}

It might be a pain in the neck to implement the Connection and Statement wrappers directly, as those interfaces define a lot of methods. It can be easier if you've already wrapped your JDBC accesses with something else, or if you're using a mock library that can create partial mocks (like Mockito's spy()). If you don't do either of those, using java.reflect.Proxy might help.