Search code examples
sqlpostgresqljdbchsqldbdatabase-independent

SQL query for day of week in Java EE - HSQLDB vs Postgres

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?

Solution

  • 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'