H2 Database - mapping between Java's OffsetDateTime and h2 type

Question

I am using H2 Database for unit tests.

According to the official H2 documentation, there is no mapping between Java's OffsetDateTime and h2 data types (TIMESTAMP WITH TIME ZONE maps to java's H2 TimestampWithTimezone type, which is not what I need).

Is there any way to achieve this?

Answer 1

While it is true that a plain getObject(int columnIndex) will return an org.h2.api.TimestampWithTimeZone object, using getObject(int columnIndex, Class<T> type) to return a java.time.OffsetDateTime seems to work fine for me using H2 version 1.4.196:

package h2test;

import java.sql.*;
import java.time.OffsetDateTime;

public class H2testMain {

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:test")) {
            Statement st = conn.createStatement();
            st.execute("CREATE TABLE TEST (ID INT PRIMARY KEY, TWTZ TIMESTAMP WITH TIME ZONE)");
            st.execute("INSERT INTO TEST (ID, TWTZ) VALUES (1, '1981-02-03 19:20:21-02:00')");
            ResultSet rs = st.executeQuery("SELECT TWTZ FROM TEST WHERE ID=1");
            rs.next();
            OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class);
            System.out.println(odt.getClass().getName());  // java.time.OffsetDateTime
            System.out.println(odt.toString());  // 1981-02-03T19:20:21-02:00
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }

}