Invalid interval format exception in hsqldb but query works in Postgres

Question

I'm running a query in postgresql. It works fine in psql but for the purpose of unit testing I created a inmemory HSQLDB and executed the same query in it. But "Invalid interval format" exception is thrown.Since I'm new to hsqldb so I cant figure out the root cause of the problem.

QUERY:

INSERT INTO microbatch_redrive
SELECT mii.*,(to_char(CURRENT_TIMESTAMP ,'yyyy-mm-ddThh:mi:ss.msZ')) FROM
(SELECT * FROM
  ( SELECT mi.*,me.extractid FROM
    (SELECT * FROM microbatch_info WHERE
        ((numattempts<= ${maxNumOfAttempts})
        AND (starttime>= '${starttime}') 
        AND (starttime<= '${endtime}')))
    AS mi LEFT OUTER JOIN microbatch_extract_info AS me ON mi.microbatchid = me.microbatchid) 
    AS mime WHERE((mime.raverunstatus='SUCCEEDED' AND mime.extractid is null)
    OR (mime.starttime < to_char(CURRENT_TIMESTAMP,'yyyy-mm-ddThh:mi:ss.msZ') AND mime.raverunstatus='NOT_STARTED')
    OR (mime.raverunstatus = 'FAILED')
    OR (mime.raverunstatus='STARTED' AND mime.starttime< (to_char(CURRENT_TIMESTAMP - INTERVAL '1 hour' * ${extractTimeIntervalInHour},'yyyy-mm-ddThh:mi:ss.msZ')))
    )
) AS mii LEFT OUTER JOIN
(SELECT microbatchid FROM microbatch_redrive WHERE raverunstatus='REDRIVE_SUCCEEDED') AS mr ON mii.microbatchid=mr.microbatchid WHERE mr.microbatchid IS NULL;

ERROR:

 Caused by: org.hsqldb.HsqlException: data exception: invalid interval format
[junit]     at org.hsqldb.error.Error.error(Unknown Source)
[junit]     at org.hsqldb.error.Error.error(Unknown Source)
[junit]     at org.hsqldb.types.IntervalType.getIntervalType(Unknown Source)
[junit]     at org.hsqldb.ParserBase.readIntervalType(Unknown Source)
[junit]     at org.hsqldb.ParserBase.readDateTimeIntervalLiteral(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readSQLFunction(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readFunction(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadRowOrCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadRowValuePredicand(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSubqueryTableBody(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSubqueryTableBody(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadTableSubqueryOrNull(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
[junit]     at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
[junit]     at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
[junit]     at org.hsqldb.ParserCommand.compilePart(Unknown Source)
[junit]     at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
[junit]     at org.hsqldb.Session.executeDirectStatement(Unknown Source)
[junit]     at org.hsqldb.Session.execute(Unknown Source)

Thanks in advance.

Answer 1

INTERVAL '1 hour' is a Postgres specific interval syntax.

In standard SQL you would need: INTERVAL '1' hour which is also supported by HSQLDB (and Postgres if you need a statement that works with both products).

So the complete expression should be:

CURRENT_TIMESTAMP - INTERVAL '1' hour * ${extractTimeIntervalInHour}