将 Oracle 日期算术转换为在 HSQLDB 中工作

发布于 2024-08-16 06:06:42 字数 522 浏览 13 评论 0原文

我正在尝试使用 hsqldb 和 dbunit 对 Oracle 支持的数据库进行现场测试，但遇到了障碍。

问题在于以下 EJB-QL（稍微简化了一点）：

SELECT o 
FROM Offer o 
WHERE :nowTime 
  BETWEEN o.startDate AND o.startDate + 7

这似乎只适用于 Oracle 的 SQL 版本。

对我来说，将其转换为在 hsqldb 和 oracle 中工作的最简单方法是什么？假设将参数之间的两个更改为命名参数是一个非常困难的重构，所以我将倾向于提供更标准化的模拟的答案

o.startdate + 7

编辑：经过更多研究后，Oracle 似乎将上面的代码片段转换为

o.startdate + INTERVAL '7' DAY

which is apparently more standard, but doesn't work in HSQLDB.

原文

I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag.

The problem is with the following EJB-QL (simplified a bit):

SELECT o 
FROM Offer o 
WHERE :nowTime 
  BETWEEN o.startDate AND o.startDate + 7

This seems to only work in Oracle's version of SQL.

What's the easiest way for me to convert this to work in both hsqldb and oracle? Assume that changing the two between arguments to named parameters is a very difficult refactor, so I'm going to favor answers that provides a more standardized analog to

o.startdate + 7

EDIT:
After doing some more research, it looks like Oracle converts the above snippet to

o.startdate + INTERVAL '7' DAY

which is apparently more standard, but doesn't work in HSQLDB.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等往事风中吹 2024-08-23 06:06:42

HSQLDB 2.0（2010年发布）支持该语法。

SELECT o 
FROM Offer o 
WHERE nowTime 
  BETWEEN o.startDate AND o.startDate + 7 DAY

它还支持

o.startdate + INTERVAL '7' DAY

更新：

在HSQLDB 2.2以来引入的ORA兼容模式中，还支持表达式o.startDate + 7。

HSQLDB 2.0 (released in 2010) supports the syntax.

SELECT o 
FROM Offer o 
WHERE nowTime 
  BETWEEN o.startDate AND o.startDate + 7 DAY

It also suppports

o.startdate + INTERVAL '7' DAY

Update:

In ORA compatibility mode introduced since HSQLDB 2.2, the expression o.startDate + 7 is also supported.

回复收藏 0 原文

ゝ偶尔ゞ 2024-08-23 06:06:42

你的方法似乎是正确的。我不知道你有什么问题。这是使用以下脚本在 Oracle 和 HSQLDB 中运行的：

CREATE TABLE OFFER (ID INTEGER, STARTDATE DATE);
INSERT INTO OFFER (ID, STARTDATE) VALUES (1, DATE '2009-01-01');
SELECT ID, STARTDATE, STARTDATE + INTERVAL '7' DAY FROM OFFER;

在这两种环境中，我都得到显示 1 月 1 日和 8 日的结果。

Your methodology seems correct. I don't know what you're having trouble with. This is working in Oracle and HSQLDB using the script below:

CREATE TABLE OFFER (ID INTEGER, STARTDATE DATE);
INSERT INTO OFFER (ID, STARTDATE) VALUES (1, DATE '2009-01-01');
SELECT ID, STARTDATE, STARTDATE + INTERVAL '7' DAY FROM OFFER;

In both environments I get the results showing the first and the eighth of January.

回复收藏 0 原文