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?

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'