Incorrect conversion of OffsetDateTime values

[alexbudarov]

trafficstars

Jmix 1.3.3 Project: homework-tickets.zip

Entity attribute:

    @NotNull
    @Column(name = "TAKE_OFF_DATE", nullable = false)
    private OffsetDateTime takeOffDate;

flight-browse.xml has a custom filter in the screen, coordinated with DataLoadCoordinator.

                            <c:jpql>
                                <c:where>e.takeOffDate >= :component_takeOffFromField</c:where>
                            </c:jpql>
                            <c:jpql>
                                <c:where><![CDATA[
                                e.takeOffDate < :component_takeOffToField
                                ]]></c:where>
                            </c:jpql>

Fields:

            <dateField id="takeOffFromField" caption="Take off from" datatype="offsetDateTime"/>
            <dateField id="takeOffToField" caption="Take off to" datatype="offsetDateTime"/>

But if you try how filter works in the running application, it will be wrong by 4 hours.

[alexbudarov]

Database: hsqldb.

[Flaurite]

The second case:

0. Launch the demo application from the issue above.
1. Try to save OffestDateTime with time: 10:00.
2. Read this instance from DB.

ER dd/MM/yyyy 10:00 +4 AR dd/MM/yyyy 14:00 +4

After some debug it turns out that OffsetDateTime object is correct before saving.

But in the DB it has increased hours by offset. I tried to reproduce the issue with SpringBoot and EclipseLink with hsqldb, but it works fine. So probably, we have issue in our fork of EclipseLink or in Jmix Data module.

Demo project: eclipse-offset.zip

[gorbunkov]

See also https://github.com/jmix-framework/jmix/issues/153

[Gavrilov-Ivan]

See also https://github.com/jmix-framework/jmix/issues/345

[Gavrilov-Ivan]

Works fine with 'org.hsqldb:hsqldb:2.7.1'

[Gavrilov-Ivan]

Found date change within AbstractDataStore#save(SaveContext) process (in hsqldb library): Initially it saves with correct dates. Last step of the save process is to reload and return all entities we have just saved (if 'discardSaved' is false): loadAllAfterSave(SaveContext context, Set<Object> savedEntities). In the end we go to ResultSet processing by hsqldb driver. It internally extracts timestamp with tz field data as a structure with seconds, nanos and offset-seconds. It is converted to OffsetDateTime the following way:

ZoneOffset zone = ZoneOffset.ofTotalSeconds(offsetSeconds);
LocalDateTime ldt = LocalDateTime.ofEpochSecond(seconds, nanos, zone);
OffsetDateTime odt = OffsetDateTime.of(ldt, zone);

But there is a difference between current 2.5.2 and last 2.7.1 version of driver:

• 2.5.2: seconds contains amount of seconds of actual local DateTime (in our target TZ) but treated as it in UTC. e.g. we initialy store 16:00 +04:00 but load seconds of 16:00 +00:00. Because of that we have an extra shift on the value of TZ offset.
• 2.7.1 seconds contains correct amount of seconds related to 16:00 +04:00 or 12:00 +00:00

[konyashkina]

Tested on Jmix version: 2.0.999-SNAPSHOT Jmix Studio plugin version: 2.0.SNAPSHOT5190-231