Math operations with intervals

[btqr]

In version 1.4.197 I could run query like this:
select (DATE '2019-05-04' - DATE '2019-03-12' - 23);
and the output was number of days and that's also how it works on PostgreSQL database.

In the newest versions (1.4.198 and greater) when I run this query I get following answer:
Feature not supported: "INTERVAL DAY - INTEGER"

I know I can achieve same result with extract function, but it would require many changes in my application. Is it any workaround to upgrade the version of database and make this query works?

[katzyn]

No, you can only change the sources of H2 by itself or change your queries.

Current behavior of H2 is correct here, the result of subtraction of two date values must be a daytime interval value. PostgreSQL violates the SQL Standard for the DATE data type only (it evaluates the subtraction between TIME or TIMESTAMP values correctly and returns a interval as expected). Old unsupported versions of H2 simply don't have the INTERVAL data type yet, so they also return an integer value for compatibility with some queries from Oracle.

[manticore-projects]

I stumbled over this just by myself and solved it like that:

select Cast({d '2019-05-04'} - {d '2019-03-12'} AS DECIMAL) - 23 from dual;

Should be also Oracle and MSQL compatible.
Cheers