Sample Header Ad - 728x90

converting/adapting time postgres SQL to H2

1 vote
0 answers
1132 views
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?
Asked by munHunger (121 rep)
Feb 22, 2021, 08:02 AM