Use CURRENT_DATE() in @Formula in Oracle and H2

Question

My previous question was closed as duplicate

Use sysdate in @Formula in Oracle and H2

Proposed solution:

How to use current date in H2 database SQL query

I've tried that CURRENT_TIMESTAMP:

@Formula("FLOOR(CURRENT_TIMESTAMP() - last_date)")
private Long daysSinceLastDate;

It's working in integration tests with embedded H2 database, but in running application Oracle:

Caused by: java.sql.SQLException: ORA-30088: datetime/interval precision is out of range

CURRENT_TIMESTAMP() is not working at all in Oracle, and CURRENT_TIMESTAMP is not working in H2.

Uladzislau Stryzhonak · Answer 1 · 2020-10-30T10:59:40.363

I had the same issue within @Where clause. I needed smth like

@Entity
@Where(clause = "open_date <= current_date")
public class SomeEntity {

    // omitted

}

and as both Oracle and H2 had function with input precision for current_timestamp, this

@Entity
@Where(clause = "open_date <= current_timestamp(6)")
public class SomeEntity {

    // omitted

}

worked for me. 6 here is the default value, so it's equivalent to current_timestamp in Oracle and current_timestamp() in H2.

score 0 · Answer 2 · answered Dec 10 '19 at 11:08

0

If you can build H2 from the current sources, you can use

CAST(CURRENT_DATE - LAST_DATE AS INT)

or Oracle-specific

FLOOR(SYSDATE - LAST_DATE)

from your previous question.

Oracle 18c also accepts both variants.

You can get sources of H2 on the GitHub:

https://github.com/h2database/h2database

Use the jar target as described here:

https://h2database.com/html/build.html#building

Don't use the Maven build of H2, it is experimental only.

answered Dec 10 '19 at 11:08

Evgenij Ryazanov

6,960
2
10
18

Thank you, but I gone for different approach. I will add an answer – mdziob Dec 11 '19 at 13:27

score 0 · Accepted Answer · answered Dec 11 '19 at 13:36

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;
        }
    };
}

Use CURRENT_DATE() in @Formula in Oracle and H2

3 Answers3