PersistentDateTimeAndZone for '2014-03-09T09:59:18.628+07:00' throws org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T08:59:18.628 (America/Winnipeg)

atangsutisna commented 10 years ago

PersistentDateTimeAndZone throws org.joda.time.IllegalInstantException during daylight davings time 'gap'. It seems the logic to convert the instant from specified timezone to system timezone to UTC is incorrect in such cases.

This has been discussed before, it seems this need fixing for DateTime as well:

Thanks for the new version.

In my opinion the conversion from Date to LocalDate in 3.0.0CR1 (and also older versions) is incorrect. The current implementation takes timezones in account but a LocalDate has no timezone. DateColumnLocalDateMapper converts a date to a string and then convert this to a DateTime:
DateTimeZone.setDefault(DateTimeZone.forID("Europe/Amsterdam"));
new DateTimeFormatterBuilder().appendPattern("yyyy-MM-dd").toFormatter().parseDateTime("1916-05-01")
This results in java.lang.IllegalArgumentException: Cannot parse "1916-05-01": Illegal instant due to time zone offset transition (Europe/Amsterdam).

Correct code should be: new LocalDate("1916-05-01"). LocalDate is aware that timezones should not be accounted for and correctly transforms the date.

March 15, 2012 | Paul Middelkoop Thanks for the feedback. I have tested and can reproduce the issue. It seems to apply only to certain historical dates (I think where the timezone offset has changed), although it could arise in the future. The fix is in svn and will go into the next release.

March 17, 2012 | Chris (@chrisphe)

The DateTime property:

@Basic()
@Columns(columns = { @Column(name = "creationtime", nullable = false),
        @Column(name = "creationtime_zone", nullable = false) })
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTimeAndZone")
private DateTime creationTime = null;

Test case:

creationTime property value that triggers bug: 2014-03-09T09:59:18.628+07:00
- also triggers the bug: 2014-03-09T02:59:18.628Z
- also triggers the bug: 2014-03-08T20:59:18.628-06:00
Reproduced at Tue Jul 15 03:16:59 CDT 2014 using System timezone: America/Winnipeg
Using UserType 3.1.0.
PostgreSQL 9.3

SQL:

/* insert id.co.bippo.schema.inventorybalance._1.StockReservationLine
    */ insert 
    into
        StockReservationLine
        (creationtime, creationtime_zone, modificationtime, modificationtime_zone, origin_id, positioner, product_currency, product_description, product_id, product_listingimageid, product_localsku, product_name, product_price, product_primaryimageid, product_qtyunit, product_shop_id, product_shop_name, product_shop_photoid, product_shop_slug, product_sideimageid, product_sku, product_slug, product_type, qty, qtyunit, stockreservation_id, variant_barcode, variant_currency, variant_description, variant_id, variant_key1, variant_key2, variant_key3, variant_key4, variant_key5, variant_listingimageid, variant_localsku, variant_name, variant_price, variant_primaryimageid, variant_qtyunit, variant_sideimageid, variant_sku, variant_slug, variant_value1, variant_value2, variant_value3, variant_value4, variant_value5, id) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Error:

org.springframework.dao.InvalidDataAccessApiUsageException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T08:59:18.628 (America/Winnipeg); nested exception is org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T08:59:18.628 (America/Winnipeg)
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384) ~[spring-orm-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:157) ~[spring-orm-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:519) ~[spring-orm-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:478) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:272) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at com.sun.proxy.$Proxy137.add(Unknown Source) ~[$Proxy137.class:na]
    at id.co.bippo.inventory.shell.InsertStockReservationTestCommand.doExecute(InsertStockReservationTestCommand.java:88) ~[classes/:na]
    at org.apache.karaf.shell.console.AbstractAction.execute(AbstractAction.java:33) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at org.soluvas.commons.shell.ExtCommandSupport.execute(ExtCommandSupport.java:86) ~[classes/:na]
    at org.apache.felix.gogo.commands.basic.AbstractCommand.execute(AbstractCommand.java:35) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at org.apache.felix.gogo.runtime.Closure.executeCmd(Closure.java:477) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.executeStatement(Closure.java:403) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Pipe.run(Pipe.java:108) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:183) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:120) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.CommandSessionImpl.execute(CommandSessionImpl.java:89) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.karaf.shell.console.jline.Console.run(Console.java:172) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_05]
    at blast.shell.karaf.ssh.BlastShellFactoryImpl$ShellImpl$2.doRun(BlastShellFactoryImpl.java:130) [blast-shell-karaf-ssh-0.13.jar:na]
    at blast.shell.karaf.ssh.BlastShellFactoryImpl$ShellImpl$2.run(BlastShellFactoryImpl.java:125) [blast-shell-karaf-ssh-0.13.jar:na]
Caused by: org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T08:59:18.628 (America/Winnipeg)
    at org.joda.time.chrono.ZonedChronology.localToUTC(ZonedChronology.java:142) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.chrono.ZonedChronology.getDateTimeMillis(ZonedChronology.java:118) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.chrono.AssembledChronology.getDateTimeMillis(AssembledChronology.java:133) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.base.BaseDateTime.<init>(BaseDateTime.java:254) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.DateTime.<init>(DateTime.java:516) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.LocalDateTime.toDateTime(LocalDateTime.java:742) ~[joda-time-2.3.jar:2.3]
    at org.joda.time.LocalDateTime.toDateTime(LocalDateTime.java:727) ~[joda-time-2.3.jar:2.3]
    at org.jadira.usertype.dateandtime.joda.columnmapper.TimestampColumnLocalDateTimeMapper.toNonNullValue(TimestampColumnLocalDateTimeMapper.java:64) ~[usertype.core-3.1.0.GA.jar:na]
    at org.jadira.usertype.dateandtime.joda.columnmapper.TimestampColumnLocalDateTimeMapper.toNonNullValue(TimestampColumnLocalDateTimeMapper.java:29) ~[usertype.core-3.1.0.GA.jar:na]
    at org.jadira.usertype.spi.shared.AbstractMultiColumnUserType.nullSafeSet(AbstractMultiColumnUserType.java:160) ~[usertype.spi-3.1.0.GA.jar:na]
    at org.hibernate.type.CompositeCustomType.nullSafeSet(CompositeCustomType.java:234) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2843) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3121) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:77) ~[hibernate-entitymanager-4.3.5.Final.jar:4.3.5.Final]
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:515) ~[spring-orm-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:478) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:272) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
    at com.sun.proxy.$Proxy137.add(Unknown Source) ~[$Proxy137.class:na]
    at id.co.bippo.inventory.shell.InsertStockReservationTestCommand.doExecute(InsertStockReservationTestCommand.java:88) ~[classes/:na]
    at org.apache.karaf.shell.console.AbstractAction.execute(AbstractAction.java:33) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at org.soluvas.commons.shell.ExtCommandSupport.execute(ExtCommandSupport.java:86) ~[classes/:na]
    at org.apache.felix.gogo.commands.basic.AbstractCommand.execute(AbstractCommand.java:35) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at org.apache.felix.gogo.runtime.Closure.executeCmd(Closure.java:477) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.executeStatement(Closure.java:403) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Pipe.run(Pipe.java:108) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:183) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:120) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.felix.gogo.runtime.CommandSessionImpl.execute(CommandSessionImpl.java:89) ~[org.apache.felix.gogo.runtime-0.10.0.jar:na]
    at org.apache.karaf.shell.console.jline.Console.run(Console.java:172) ~[org.apache.karaf.shell.console-2.2.9.jar:na]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_05]
    at blast.shell.karaf.ssh.BlastShellFactoryImpl$ShellImpl$2.doRun(BlastShellFactoryImpl.java:130) [blast-shell-karaf-ssh-0.13.jar:na]
    at blast.shell.karaf.ssh.BlastShellFactoryImpl$ShellImpl$2.run(BlastShellFactoryImpl.java:125) [blast-shell-karaf-ssh-0.13.jar:na]
    ... 22 more

Tag @ceefour

atangsutisna commented 10 years ago

Workaround: Switch your system timezone :( I tried America/Caracas and it "works".

I'm confused because Jadira tried to use 2014-03-09T08:59:18.628 (America/Winnipeg) which is wrong. The correct times are:

2014-03-09T09:59:18.628+07:00
2014-03-09T02:59:18.628Z
2014-03-08T20:59:18.628-06:00 (e.g. America/Winnipeg)

rudiw commented 10 years ago

:+1: for this, happens to me:

179/523 #17900 Σ100 Migrating from mon… ❱❱❱❱❱❱❱❱❱❱❱❱❱❱❱❱❱❱❱ 100.0% 39:26 ⌛00:00  ERROR  
Error executing command:
  Cannot process StockReservationImpl 33/100 (offset 17933) in page 179 out of 523 (offset 17900 out of 52259) in batch job 'Migrating from mongoDB to PostreSQL':
    org.springframework.dao.InvalidDataAccessApiUsageException:
      Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T07:47:25.091 (America/Montreal); nested exception is
        org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T07:47:25.091 (America/Montreal)

Server is using America/Montreal timezone at this time. The workaround "switching the system timezone" is a very risky proposition. :(

chrisphe commented 9 years ago

Please can you advise the steps to reproduce using the existing unit test suite (or a new test).

Thanks Chris

chrisphe commented 9 years ago

Also do you have the issue on 3.2.0. There is a slightly different logic there (although that approach doesn't work on all databases either. See #35

chrisphe commented 9 years ago

Closing as there has been no update since March and much of this has been reworked in recent releases.

JadiraOrg / jadira

PersistentDateTimeAndZone for '2014-03-09T09:59:18.628+07:00' throws org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2014-03-09T08:59:18.628 (America/Winnipeg) #24