DATE_SUB and DATE_ADD in H2 for MySQL

Question

I am using MySQL as my database and H2 for testing. I am also using playframework 2.3.x and Scala, but I think does not matter for the question purpose.

H2 has a conflict with some functions that I am using in a query

SELECT *
FROM subscriptions
WHERE active_until >= (DATE_SUB(CURDATE(), INTERVAL 3 DAY))
AND active_until <= (DATE_ADD(CURDATE(), INTERVAL 1 DAY))
AND status = "ACTIVE"

The functions that cause the problem are DATE_SUB and DATE_ADD.

Is there a workaround where I can make this work or change the query without breaking it for mysql?

Pest

I gave up on trying to use H2 for testing, as the difference between the two flavors of SQL was too much of a hassle to deal with. – Torhert

Answer 1

Finally I was able to solve it. I had to change DATE_ADD for TIMESTAMPADD and DATE_SUB with TIMESTAMPDIFF. Then I changed CURDATE() for CURRENT_DATE. Also the sign of the method change but works on both H2 and My.

SELECT *
FROM subscriptions
WHERE active_until >= (TIMESTAMPDIFF(DAY, 3, CURRENT_DATE))
AND active_until <= (TIMESTAMPADD(DAY, 1, CURRENT_DATE))
AND status LIKE 'ACTIVE'

Pest

I'm not sure if TIMESTAMPDIFF is what you want here because it is supposed to get the difference between two timestamps and not subtract a time interval from a date (see: h2database.com/html/functions.html) – Concentric

but do TIMESTAMPDIFF and TIMESTAMPADD work for both, h2 and MySQL? – Undrape

Answer 2

this is my example. TIME_TO_BEGIN is TIMESTAMP type. "DATEADD" function name and parameters like "MONTH" should all be upper cased.

CREATE TABLE BLOCKED_BIN_RANGE (
  BLOCKED_BIN_RANGE_ID BIGINT not null,
  TIME_TO_BEGIN TIMESTAMP not null,
  USER_NAME VARCHAR(30)  not null 
);

Insert into BLOCKED_BIN_RANGE (BLOCKED_BIN_RANGE_ID,TIME_TO_BEGIN, USER_NAME)
        values (304, DATEADD('MONTH', 1, CURRENT_TIMESTAMP), 'BILL');

Spoonful