How to use current date in H2 database SQL query
use CURRENT_TIMESTAMP
select * from tableName where date_column > CURRENT_TIMESTAMP()
- CURRENT_TIMESTAMP
Use CURRENT_DATE() in @Formula in Oracle and H2
I've ended with adding interceptor in integration tests datasource configuration.
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabase(Database.H2);
vendorAdapter.setGenerateDdl(false);
vendorAdapter.setShowSql(true);
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
entityManagerFactoryBean.setDataSource(dataSource());
entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
Properties properties = new Properties();
properties.setProperty(Environment.FORMAT_SQL, "false");
**properties.put(Environment.INTERCEPTOR, hibernateInterceptor());**
entityManagerFactoryBean.setJpaProperties(properties);
return entityManagerFactoryBean;
}
@Bean
public EmptyInterceptor hibernateInterceptor() {
return new EmptyInterceptor() {
@Override
public String onPrepareStatement(String sql) {
String query = super.onPrepareStatement(sql);
query = query.replace("projaudit0_.sysdate", "CURRENT_DATE()");
return query;
}
};
}
Insert a DATE into H2 database
We can try to use ISO_8601 format for DateTime inserted value.
INSERT INTO CUSTOMERS (
ID,
FIRSTNAME,
LASTNAME,
GENDER,
COMPANYID,
EMAIL,
BIRTHDAY,
CREATEDAT,
UPDATEDAT
)
VALUES (
1,
'Lee',
'Diaz',
'male',
'159',
'vel.sapien.imperdiet@protonmail.ca',
'1912-05-08',
'2019-13-12',
'2021-30-09'
)
Auto generate primary key as current date time in H2 database
You can try this in the H2 console:
call formatdatetime(now(),'yyyyMMddHHmmssSSS');
This will give you a properly formatted string. Now you need to convert it to bigint.
call cast(formatdatetime(now(),'yyyyMMddHHmmssSSS') as bigint);
Last step: change your SQL accordingly...
CREATE TABLE TEST_TABLE(
ID BIGINT DEFAULT CAST(FORMATDATETIME(CURRENT_TIMESTAMP(), 'yyyyMMddHHmmssSSS') AS BIGINT) PRIMARY KEY,
NAME VARCHAR(255)
);
Tested on H2 1.3 and 1.4.
Word of caution: please use transactions and space your insertions...
H2 Get Date 1 day old from current time
Simply subtract the number of days from current_date
select *
from the_table
where the_date_column < current_date - 1;
The above would work in Postgres just as well.
H2 DB SQL, how to make a variable of the yersterday's date and call it after
You need to use a user-defined variable:
SET @YesterdayDate = CURRENT_DATE - INTERVAL '1' DAY;
INSERT INTO dish(id, name, date_added, price, restaurant_id)
VALUES (14, 'SomeDish', @YesterdayDate, 120, 2);
These variables aren't persisted and they are visible only in the current session. They are prefixed with the @
symbol.
If you need to use the variable in other sessions, you can create a constant instead:
CREATE CONSTANT YesterdayDate VALUE CURRENT_DATE - INTERVAL '1' DAY;
INSERT INTO dish(id, name, date_added, price, restaurant_id)
VALUES (14, 'SomeDish', YesterdayDate, 120, 2);
-- After use
DROP CONSTANT YesterdayDate;
Constants are persisted into database and they're visible in all sessions. Their names are used as is.
Values of user-defined variables and constants will not be updated automatically for the next day.
These variables and constants aren't portable across different DBMS.
Related Topics
SQL Server Equivalent to Oracle's Nulls First
Oracle - Why Does the Leading Zero of a Number Disappear When Converting It To_Char
SQL Server Bitwise Processing Like C# Enum Flags
Export All Ms Access SQL Queries to Text Files
Rails Includes with Conditions
How to Select Rows for a Specific Date, Ignoring Time in SQL Server
SQL Join Where to Place the Where Condition
How to Drop All Foreign Key Constraints in All Tables
MySQL Statement Combining a Join and a Count
Get All Characters Before Space in MySQL
Delete with "Join" in Oracle SQL Query
Comparing Results with Today's Date
How to Concatenate Text in a Query in SQL Server
How to Export Data with Oracle SQL Developer
Dealing with System.Dbnull in Powershell
Postgresql: Top N Entries Per Item in Same Table
Exec Stored Procedure into Dynamic Temp Table
Does a Select Query Always Return Rows in the Same Order? Table with Clustered Index