使oracle的last_day函数在oracle和h2之间兼容

Question

我在java项目中有一个sql查询。它必须在 oracle 和 h2 中执行，具体取决于客户端及其 DBMS 选择。

它必须返回值，其中 sent_date 是月份减一（上个月注册表）

SELECT * FROM TABLE WHERE SENT_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) 
AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)) ORDER BY ELAPSED_TIME DESC

问题是，LAST_DAY 函数是 Oracle 特定函数，H2 中不存在，并且 H2 日期管理有时在 Oracle 中不起作用，但我需要它完全兼容。

我们怎样才能做到这一点？

Answer 1

它可能会更高兴：SELECT * FROM TABLEWHERE SENT_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) AND TRUNC(SYSDATE,'mm') - 1ORDER BY ELAPSED_TIME DESC表明它们评估为相同的值：SELECT ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) as orig_start,&nbsp; LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)) as orig_end,&nbsp; TRUNC(SYSDATE,'mm') - 1 as new_endFROM dual;ORIG_START&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORIG_END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NEW_END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;------------------- ------------------- -------------------2019-08-01 00:00:00 2019-08-31 00:00:00 2019-08-31 00:00:00但请记住，这between是包容性的，因此，如果您sent_date有非午夜时间，则会排除 2019-08-31 00:00:01 到 2019-08-31 23:59:59 之间的任何内容。这样做更安全：SELECT * FROM TABLEWHERE SENT_DATE >= ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)AND SENT_DATE < TRUNC(SYSDATE,'mm')ORDER BY ELAPSED_TIME DESC其中将包括 2019-08-01 00:00:00 或之后的所有内容，以及直到（但不包括）2019-09-01 00:00:00 的所有内容 - 这样就不会出现缺失的一天。或者，如果它不能识别，add_months也许：SELECT * FROM TABLEWHERE SENT_DATE >= TRUNC(SYSDATE,'mm') - INTERVAL '1' MONTHAND SENT_DATE < TRUNC(SYSDATE,'mm')ORDER BY ELAPSED_TIME DESC（我无法测试其中任何一个是否在 H2 中实际工作...... *8-）

Answer 2

LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1))可以简化为TRUNC(SYSDATE,'mm') - 1没有LAST_DAY功能，但 H2 也不支持带有 'mm' 参数的非标准TRUNC。所以你也需要避免它。这样的查询将与两个数据库兼容（但是，您需要最新版本的 H2，例如 1.4.199）：SELECT * FROM tableName WHERE SENT_DATE BETWEEN&nbsp; &nbsp; CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) - INTERVAL '1' MONTHAND&nbsp; &nbsp; CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)ORDER BY ELAPSED_TIME DESC请注意，这个查询不是标准的，我故意使用Oracle风格的日期算法来简化查询，它是H2支持的。ADD_MONTH如果您愿意，也可以在此处使用非标准。如果您只需要使用标准语法来兼容更多数据库，则可以避免使用 Oracle/H2 风格的算术：SELECT * FROM tableName WHERE SENT_DATE BETWEEN&nbsp; &nbsp; CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) * INTERVAL '1' DAY - INTERVAL '1' MONTHAND&nbsp; &nbsp; CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) * INTERVAL '1' DAYORDER BY ELAPSED_TIME DESC另请注意，Oracle与其他数据库不同，没有正常的DATE数据类型，其DATE类型可能有时间（H2可以在Oracle兼容模式下模拟这种偏差）。因此，只有当您确保日期都在 时，所有这些带有“BETWEEN”的查询（包括最初的仅 Oracle 查询）才能正常工作00:00:00。如果您的日期还包含时间部分，您需要使用几个比较运算符：SELECT * FROM tableName WHERE SENT_DATE >=&nbsp; &nbsp; CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) - INTERVAL '1' MONTHAND SENT_DATE <&nbsp; &nbsp; CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1)ORDER BY ELAPSED_TIME DESC（这里也使用了 Oracle/H2 算法，其他数据库的乘法可能INTERVAL '1' DAY是必要的。）