java.lang.IllegalArgumentException: Parameter value [2023-12-25T17:19:54.622Z] did not match expected type [java.time.ZonedDateTime (n/a)]

Question

I migrated this code:

import org.joda.time.DateTime;

@Entity
@Table(name = "purchases")
@Getter
@Setter
public class Purchases {

  @Column(name = "purchase_time")
  @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
  private DateTime purchaseTime;
}

to this code:

import java.time.ZonedDateTime;

@Entity
@Table(name = "purchases")
@Getter
@Setter
public class Purchases {

  @Column(name = "purchase_time")
  private ZonedDateTime purchaseTime;
}

Now I get error:

2024-01-24 17:20:00.219 ERROR 1 --- [eduler_Worker-1] .a.p.g.i.s.ProcessPendingPurchasesJob : Failed to start/complete pending job.

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [2023-12-25T17:19:54.622Z] did not match expected type [java.time.ZonedDateTime (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [2023-12-25T17:19:54.622Z] did not match expected type [java.time.ZonedDateTime (n/a)]
        at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374) ~[spring-orm-5.3.27.jar!/:5.3.27]
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235) ~[spring-orm-5.3.27.jar!/:5.3.27]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) ~[spring-orm-5.3.27.jar!/:5.3.27]
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at jdk.proxy2/jdk.proxy2.$Proxy191.findOldTransactions(Unknown Source) ~[na:na]
        at com.service.ReconciliationService.getPending(ReconciliationService.java:105) ~[classes!/:na]
        at com.service.ReconciliationService.processPending(ReconciliationService.java:52) ~[classes!/:na]
        at com.service.ReconciliationService$$FastClassBySpringCGLIB$$686fd8d1.invoke(<generated>) ~[classes!/:na]
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at com.service.ReconciliationService$$EnhancerBySpringCGLIB$$d9962f8a.processPending(<generated>) ~[classes!/:na]
        at com.scheduler.ProcessPendingPurchasesJob.executeInternal(ProcessPendingPurchasesJob.java:58) ~[classes!/:na]
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75) ~[spring-context-support-5.3.27.jar!/:5.3.27]
        at org.quartz.core.JobRunShell.run(JobRunShell.java:202) ~[quartz-2.3.2.jar!/:na]
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) ~[quartz-2.3.2.jar!/:na]
Caused by: java.lang.IllegalArgumentException: Parameter value [2023-12-25T17:19:54.622Z] did not match expected type [java.time.ZonedDateTime (n/a)]
        at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:27) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:90) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:55) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:501) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:122) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.setParameter(CriteriaQueryTypeQueryAdapter.java:393) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.setParameter(CriteriaQueryTypeQueryAdapter.java:61) ~[hibernate-core-5.6.15.Final.jar!/:5.6.15.Final]
        at org.springframework.data.jpa.repository.query.QueryParameterSetter$BindableQuery.setParameter(QueryParameterSetter.java:319) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$3(QueryParameterSetter.java:116) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$1.execute(QueryParameterSetter.java:141) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:116) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:82) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:74) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:96) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.invokeBinding(PartTreeJpaQuery.java:324) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.createQuery(PartTreeJpaQuery.java:243) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.doCreateQuery(PartTreeJpaQuery.java:106) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:227) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143) ~[spring-data-jpa-2.6.10.jar!/:2.6.10]
        at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.6.10.jar!/:2.6.10]
        at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.6.10.jar!/:2.6.10]
        at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:160) ~[spring-data-commons-2.6.10.jar!/:2.6.10]
        at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139) ~[spring-data-commons-2.6.10.jar!/:2.6.10]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.27.jar!/:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar!/:5.3.27]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.27.jar!/:5.3.27]
        ... 19 common frames omitted

Do you know what should be the proper field type?

Answer 1

Most JDBC drivers do not support ZonedDateTime and Instant. You can check this section on the PostgreSQL JDBC documentation page for further information. The last section of this page also describes the mapping of ANSI SQL types with java.time types.

So, while all of Instant, ZonedDateTime and OffsetDateTime can parse the string, 2023-12-25T17:19:54.622Z as shown in this demo, you should use OffsetDateTime for the purchaseTime field.

---

^{You can also check this answer and this answer regarding the use of java.time types with JDBC.}