Search code examples
javasqloraclehsqldbejbql

Converting Oracle date arithmetic to work in HSQLDB

I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag.

The problem is with the following EJB-QL (simplified a bit):

SELECT o 
FROM Offer o 
WHERE :nowTime 
  BETWEEN o.startDate AND o.startDate + 7

This seems to only work in Oracle's version of SQL.

What's the easiest way for me to convert this to work in both hsqldb and oracle? Assume that changing the two between arguments to named parameters is a very difficult refactor, so I'm going to favor answers that provides a more standardized analog to 

o.startdate + 7

EDIT: After doing some more research, it looks like Oracle converts the above snippet to 

o.startdate + INTERVAL '7' DAY
which is apparently more standard, but doesn't work in HSQLDB.

Solution

  • HSQLDB 2.0 (released in 2010) supports the syntax.

    SELECT o 
FROM Offer o 
WHERE nowTime 
  BETWEEN o.startDate AND o.startDate + 7 DAY

    It also suppports 

    o.startdate + INTERVAL '7' DAY

    Update:

    In ORA compatibility mode introduced since HSQLDB 2.2, the expression o.startDate + 7 is also supported.