H2, Spring boot-JPA: Issue with retrieving entity with OffsetTime field

Question

I have a small Spring Boot application (spring-data-jpa) with H2 database (version 1.4.200). I have Shift object, representing a recurring interval in time:

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class Shift {
    @Id
    @GeneratedValue
    private Long id;
    @Column(name = "start_time", columnDefinition = "TIME WITH TIME ZONE")
    private OffsetTime startTime;
    @Column(name = "end_time", columnDefinition = "TIME WITH TIME ZONE")
    private OffsetTime endTime;

    public Shift (OffsetTime startTime, OffsetTime endTime) {
        this.startTime = startTime;
        this.endTime = endTime;
    }
}

@RestController
@RequiredArgsConstructor
public class Controller {

    private final ShiftRepository shiftRepository;
    @RequestMapping("/")
    public Shift index() {
        return shiftRepository.getById(1L);
    }

}

public interface ShiftRepository extends JpaRepository<Shift, Long> {
}

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

@Configuration
public class LoadDatabase {
    @Bean
    CommandLineRunner initUserTable(ShiftRepository repository) {
        return args -> repository
                .save(
                        new Shift(OffsetTime.parse("10:15:30+00:00"), OffsetTime.parse("11:25:30+00:00")));
    }}

Retrieving it leads to:

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException: Cannot invoke ""org.h2.engine.CastDataProvider.currentTimestamp()"" because ""<parameter1>"" is null" [50000-200] at org.h2.message.DbException.getJdbcSQLException(DbException.java:505) ~[h2-1.4.200.jar:1.4.200] at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200] at org.h2.message.DbException.get(DbException.java:194) ~[h2-1.4.200.jar:1.4.200] at org.h2.message.DbException.convert(DbException.java:347) ~[h2-1.4.200.jar:1.4.200] at org.h2.message.DbException.toSQLException(DbException.java:319) ~[h2-1.4.200.jar:1.4.200] at org.h2.message.TraceObject.logAndConvert(TraceObject.java:366) ~[h2-1.4.200.jar:1.4.200] at org.h2.jdbc.JdbcResultSet.getTime(JdbcResultSet.java:480) ~[h2-1.4.200.jar:1.4.200] at com.zaxxer.hikari.pool.HikariProxyResultSet.getTime(HikariProxyResultSet.java) ~[HikariCP-3.4.5.jar:na] at org.hibernate.type.descriptor.sql.TimeTypeDescriptor$2.doExtract(TimeTypeDescriptor.java:84) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:3130) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] at org.hibernate.loader.plan.exec.process.internal.EntityReferenceInitializerImpl.loadFromResultSet(EntityReferenceInitializerImpl.java:342) ~[hibernate-core-5.4.31.Final.jar:5.4.31.Final] ... 73 common frames omitted

In debugger I see that indeed there is no timezone set:

My application.properties which could be relevant:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2

Relevant lines from pom.xml:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>15</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

Do I need to use some custom converter for TIME WITH TIME ZONE database type and OffsetTime Java type?

It works without any exception when I change type to:

    @Column(name = "start_time", columnDefinition = "TIME")
    private OffsetTime startTime;
    @Column(name = "end_time", columnDefinition = "TIME")
    private OffsetTime endTime;

Answer 1

Evgenij Ryazanov's comment above was quite helpful. I was having the same problem and was able to fix it by downgrading the version of H2, so I am elevating it here to an answer.

In your pom.xml, update to:

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <!-- There is a bug with 1.4.200 and retrieving dates back from the DB -->
        <version>1.4.199</version>
    </dependency>

Spring Boot changed from 1.4.199 in version 2.1.10. Spring Boot 2.7 uses the new 2.x version of H2. Presumably that would also be a fix, but I have not tried that out.