H2 - DATEADD for complete day

Question

this query will retrieve all records during last 7 days.

SELECT * FROM STATISTICS WHERE TIMESTAMP > DATEADD('DAY',-7, NOW())

How can I change the query to include the records from midnight 7 days ago?

ex.

SELECT DATEADD('DAY',-7, NOW())

Gives: 2013-09-25 13:46:54.372

But I would like to have: 2013-09-25 00:00:00.000

Solution

For anyone interested, here is a solution.

Using CURRENT_DATE instead of NOW() does the trick!

ex:

SELECT * FROM STATISTICS WHERE TIMESTAMP > DATEADD('DAY',-7, CURRENT_DATE)

http://www.h2database.com/html/functions.html#dateadd

Comments

[Enrico Giurin]

I used this: DATEADD('DAY',-10, CURRENT_DATE()