Search code examples
spring-bootjpah2jpql

Testing with mocked value for current_timestamp on H2

Is there a way to set a fixed value for current_timestamp on H2?

I have a test running with H2 which uses current_timestamp to determine whether or not a register is active like the following:


select t from MyTable t
where t.enabled = true
  and current_timestamp between t.startDate and t.endDate

I'd like to set a fixed (a.k.a mocked) value for current_timestamp on this test to avoid this test breaking when eventually current_timestamp is greater than e.endDate.

Is there another better way to get that running? I'd like to avoid passing current_timesamp as a query parameter.

Solution

  • You can add ;BUILTIN_ALIAS_OVERRIDE=TRUE to the JDBC URL and define an alias to your own function with

    CREATE ALIAS CURRENT_TIMESTAMP FOR "path.to.YourClass.staticMethod"

    command.

    This method normally should return java.time.OffsetDateTime (unless you use some outdated version of H2).

    H2 should be able to find your class in the classpath (on the server side if you use H2 server process).

    Instead of a Java method you can use Java code direcly:

    CREATE ALIAS CURRENT_TIMESTAMP AS
'java.time.OffsetDateTime m() { return java.time.OffsetDateTime.of(2000, 1, 1, 0, 0, 0, 0, java.time.ZoneOffset.UTC); }';

    In that case you need to have a Java compiler in the classpath.

    See CREATE ALIAS documentation for more details.