Search code examples
timestamph2sql-timestamp

Current moment in H2 database

How to get the actual current clock time, the current moment, in H2 database?

The CURRENT_TIMESTAMP function gives the moment when the current database transaction began. Is there a way to get the current moment, the time when the current statement is executing? This may be the same or later than CURRENT_TIMESTAMP.

For comparison, in Postgres, some functions such as current_timestamp return the transaction start time while some functions such as clock_timestamp return the actual current clock time.

Solution

  • Current time (of function call)

    You could create an ALIAS for System.currentTimeMillis():

    CREATE ALIAS CURRENT_TIME_MILLIS FOR "java.lang.System.currentTimeMillis";

    This wouldn't generate the timestamp of statement execution start, but really the current timestamp, whenever H2 actually calls the function - i.e. a non-deterministic moment, and perhaps a different value for different rows within the same statement.

    But perhaps, that's good enough for your requirements.

    Current time (of statement execution)

    If the above non-deterministic solution is not sufficiently precise for you, another option would be to implement a JDBC wrapper that intercepts all statements, sets the current time to some thread local (H2 doesn't support Connection.getClientInfo()):

    threadlocal.set(new Timestamp(System.currentTimeMillis()).toString());

    ... and then reads that client info from an ALIAS like this:

    public static Timestamp statementTime() throws SQLException {
    return Timestamp.valueOf(threadlocal.get());
}

    And then:

    CREATE ALIAS STATEMENT_TIME FOR "com.example.statementTime";