15

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?

agusgambina
  • 6,229
  • 14
  • 54
  • 94
  • 3
    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. – Michael Zajac May 18 '16 at 15:44

2 Answers2

19

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'
agusgambina
  • 6,229
  • 14
  • 54
  • 94
  • 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: http://www.h2database.com/html/functions.html) – digiarnie Apr 17 '19 at 05:14
  • but do TIMESTAMPDIFF and TIMESTAMPADD work for both, h2 and MySQL? – Manuelarte Jul 27 '20 at 13:45
0

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');
Janet
  • 772
  • 9
  • 13