Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

H2 - DATEADD for complete day

Tags:

date

sql

h2

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

like image 435
user955732 Avatar asked Oct 02 '13 11:10

user955732


People also ask

Is H2 database production ready?

Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing.

What is a .H2 file?

H2 is an SQL database engine that is written in Java™ that implements the JDBC API. A browser-based console application is included. The H2 database is preinstalled with the Cúram software. After you install the Cúram platform software, the self-contained database is located in the %CURAMSDEJ%\drivers\h2. jar file.

Does H2 database support stored procedures?

Stored procedure in H2 database is same as java methods.So write java methods and can invoke using aliases.


1 Answers

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

like image 198
user955732 Avatar answered Sep 23 '22 19:09

user955732