How to Use Current Date in H2 Database SQL Query

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