I have a quite large SQL that runs towards postgres in production today, and due to some performance reasons, I am converting our integration tests to unit tests.

The integration tests ran towards a docker postgres image, but it is simply way to slow to setup, so I am experimenting with an H2 in-mem database. It is as expected really quick to setup and works in most cases... However I have this statement

(timezone('America/New_York', timestamp) - ('1 hour')::interval)::date

That simply does not work in H2. One reason is that the function timezone does not exist, but that seems like an easy fix by just creating an alias to a java timezone function.

What is slightly trickier is this part ('1 hour')::interval as it results in this exception

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT (('1 HOUR')::INTERVAL)[*]::DATE FROM t"; expected "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND"; SQL statement:

And I am kind of at an loss as to if it is possible to adapt h2 to accept it. And I am saying adapt h2, because there might be other issues related to changing the SQL (that needs to run towards postgres in prod).

So is it possible to adapt h2 to accept this? and if not, is there some other way to write the sql to have it working for both postgres and h2?

and as a sidenote: does this question belong here, or at stackoverflow?

  • Even if you can adapt all the code, you would only test if the code runs correctly using H2, but you would not test if the code runs correctly on production which is far more important. phauer.com/2017/dont-use-in-memory-databases-tests-h2
    – user1822
    Feb 22, 2021 at 8:18
  • timezone('America/New_York', timestamp) is equivalent to "timestamp" at time zone 'America/New_York' a better way to write that to begin with (at least in my opinion - the timezone() function isn't even documented)
    – user1822
    Feb 22, 2021 at 8:22
  • fair points. My plan is not to stop testing against postgres. we currently have thousands of tests, and I don't think it would hurt to move some of them to H2 (while keeping some on postgres)... yeah timezone snippet is a bit cleaner. I wonder why we are using timezone() to begin with
    – munHunger
    Feb 22, 2021 at 8:36
  • I don't know why it exists. Probably something internal given the fact that it's not even documented
    – user1822
    Feb 22, 2021 at 8:56


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.