str_to_date mysql does not run in hsql

Question

My code is performing the following update at a given time in my mysql database:

" UPDATE client_registration "  +
" SET registration_date = NOW() "  +
" WHERE cycle <= str_to_date(\"" + now + "\",'%d/%m/%Y %H:%i') "; 

However I have a unit test that tries to perform this update on the HSQL database and I receive the following error message: user lacks privilege or object not found: STR_TO_DATE.

Some way to execute the condition WHERE cycle_start <= str_to_date(\"" + now + "\",'%d/%m/%Y %H:%i') for the mysql database and the hsql database?

Answer 1

You have to re-write your query for HSQL:

" UPDATE client_registration "  +
" SET registration_date = NOW() "  +
" WHERE cycle <= current_timestamp"; 

How to do "select current_timestamp" in hsqldb?

Answer 2

If you want to run the exact same query on both MySQL and HSQLDB, you need to create the STR_TO_DATE function on HSQLDB. You also need to use the single-quote character in your query: str_to_date('" + now + "','%d/%m/%Y %H:%i') (this quoting follows the SQL Standard).

It is easier if you use the SQL Standard format 'YYYY-MM-DD hh:mm:ss' (e.g '2020-07-21 14:30:00') for your 'now' variable and the format string you use with MySQL. In this case the HSQLDB function is simply created as:

CREATE FUNCTION STR_TO_DATE(STR VARCHAR(30), FORMAT VARCHAR(40) ) 
 RETURNS TIMESTAMP
 RETURN CAST(STR AS TIMESTAMP);

Execute the CREATE FUNCTION statement once when you connect to the database and you can use it in all your queries and upade statements.