英文:
str_to_date mysql does not run in hsql
问题
我的代码在给定时间在MySQL数据库中执行以下更新：
"UPDATE client_registration " +
"SET registration_date = NOW() " +
"WHERE cycle <= str_to_date('" + now + "','%d/%m/%Y %H:%i');"
然而，我有一个单元测试尝试在HSQL数据库上执行此更新，但我收到以下错误消息：用户缺乏权限或对象未找到：STR_TO_DATE
有没有办法在MySQL数据库和HSQL数据库中执行条件WHERE cycle_start <= str_to_date('" + now + "','%d/%m/%Y %H:%i')？
英文:
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?
答案1
得分: 0
你需要重新编写针对 HSQL 的查询：
"UPDATE client_registration " +
"SET registration_date = NOW() " +
"WHERE cycle <= current_timestamp";
https://stackoverflow.com/questions/909702/how-to-do-select-current-timestamp-in-hsqldb
英文:
You have to re-write your query for HSQL:
" UPDATE client_registration " +
" SET registration_date = NOW() " +
" WHERE cycle <= current_timestamp";
https://stackoverflow.com/questions/909702/how-to-do-select-current-timestamp-in-hsqldb
答案2
得分: 0
如果您想在MySQL和HSQLDB上运行完全相同的查询，需要在HSQLDB上创建STR_TO_DATE函数。您还需要在查询中使用单引号字符：str_to_date('"+now+"','%d/%m/%Y %H:%i')（此引号遵循SQL标准）。
如果您使用SQL标准格式'YYYY-MM-DD hh:mm:ss'（例如'2020-07-21 14:30:00'）作为您的'now'变量和与MySQL一起使用的格式字符串，会更容易。在这种情况下，HSQLDB函数可以简单地创建为：
CREATE FUNCTION STR_TO_DATE(STR VARCHAR(30), FORMAT VARCHAR(40) )
RETURNS TIMESTAMP
RETURN CAST(STR AS TIMESTAMP);
连接到数据库时执行一次CREATE FUNCTION语句，然后您可以在所有查询和更新语句中使用它。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论