CODEWITHSUNDEEP
javasqloracle-databasehsqldbejbql
Jon Bristow
Jon Bristow

Reputation: 1725

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.

Upvotes: 2

Views: 2553

Answers (3)

fredt
fredt

Reputation: 24352

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.

Upvotes: 4

anon
anon

Reputation:

Your methodology seems correct. I don't know what you're having trouble with. This is working in Oracle and HSQLDB using the script below:

CREATE TABLE OFFER (ID INTEGER, STARTDATE DATE);
INSERT INTO OFFER (ID, STARTDATE) VALUES (1, DATE '2009-01-01');
SELECT ID, STARTDATE, STARTDATE + INTERVAL '7' DAY FROM OFFER;

In both environments I get the results showing the first and the eighth of January.

Upvotes: 1

anon
anon

Reputation:

The HSQLDB syntax for date manipulation is quite different from Oracle. Your best chance is to write a stored procedure in the Oracle DB which simulates the behavior of the HSQLDB functions/procedures.

Upvotes: -1

Related Questions