1

I've got JavaEE app, the main one is working on docker with PostgreSQL, tests are working with HSQLDB,

In a query I want to check day of the week (to exclude weekends from gathered data)

In Postgres the following works:

extract(dow from date) != 0 AND extract(dow from date) != 6

When with HSQLDB this results in:

Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: DOW

For HSQLDB I would need to use:

DAYOFWEEK(date) != 1 AND DAYOFWEEK(date) != 7

But that of course doesn't work with PostgreSQL:

Caused by: org.postgresql.util.PSQLException: ERROR: function dayofweek(date) does not exist

Any idea how to unify this?

I guess, to set the same db-type in persistance.xml resources.xml for tests and app? But I wonder if there is any SQL function I could use for both, without re-editing xml files?

pearinthemountains
  • 115
  • 2
  • 11
  • Another good example on why running tests on a different DBMS then the production one isn't a good idea. –  Feb 09 '17 at 10:06
  • 2
    Could you try the JDBC function escape `{fn DAYOFWEEK(date)}`. In theory all JDBC drivers should unescape to the correct database specific function, but they don't always do. – Mark Rotteveel Feb 09 '17 at 10:20
  • BTW: The range of that function escape should be 1..7, with 1 being Sunday. – Mark Rotteveel Feb 09 '17 at 10:26

2 Answers2

0

I just verified Mark's suggestion to use JDBC escapes and the following works with both HSQLDB and Postgres:

{fn DAYOFWEEK(date)} <> 0 AND {fn DAYOFWEEK(date)} <> 6
0

solved:

I used JPQL NamedQuery (I was using NativeQuery) with passed parameter of list of LocalDates (with weekends + holidays),

in query, in WHERE section I've typed:

'myDay not in :listOfWeekendsAndHolidays'
pearinthemountains
  • 115
  • 2
  • 11