Cannot find a common SQL that will work both for H2 and Postgres

Question

In my project, I am using Postgres database, but sometimes, for development, I am using H2. I am trying to define an sql script which will update a timestamp column as described below, but I could not find a single format that can be applied both to Postgres and a to H2.

Basically, the sql is trying to set the timestamp to be NOW + 1 week.

Here is the sql that works for Postgres:

update mytable set mytime = CURRENT_TIMESTAMP + INTERVAL '7 days';

Here is the sql that works for H2:

update mytable set mytime = CURRENT_TIMESTAMP + 7;

Can someone suggest an single sql that can do the same for both databases?

Answer 1

We ran into a similar issue where we use PostgreSQL for our app, but our unit tests use H2. We ended up just moving the date arithmetic into the application code. So, using the example above, the sql was change to be

update mytable set mytime = ?;

And then in the application code (using Java) we figured out the time:

Instant future1Week = Instant.now().plus(1, ChronoUnit.WEEKS);

And bound that value in the PreparedStatement.

Answer 2

According to my test, this works for both H2 and PostgreSQL:

update mytable set mytime = 
cast(cast(current_timestamp as date) + 7 as timestamp) + 
cast(current_timestamp as time);

It is a bit strange I agree, but it is the only way I found with current versions of H2 and PostgreSQL.