Spring data jpa returning inconsistent values of Date from oracle database table

Question

I have a table in oracle db.The table contains a column last_updt_dt with a datatype Date.
I am using spring data jpa with hibernate version 5.3.12 final. The entity class for table contains date field as below

@Column(name = "last_updt_dt") private LocalDateTime lastUpdtDt;
If I insert date through my application and retrieve it , then it shows expected behavior. For Ex, in database, it shows following value (with 24hr format)

28-04-2020 00:32:02

and in the application, if my server timezone is AEST, then it shows the above value as

2020-04-28T10:32:02

which is correct ( the utc value in db and AEST value received in my application are matching correctly)

But, I have some existing records in the same database table. If I try to retrieve these records, then in my application, the date shows wrong value. For Ex, the database table shows following value (with 24hr format)

11-01-2017 09:36:34

and in the application, if my server timezone is AEST, then it shows the above value as

2017-01-11T20:36:34

if I convert this utc time 09:36:34 to AEST time, then it gives me time as 19:36:34 and what I am seeing here is 20:36:34 which is one hour extra.

So, I am unable to understand the behavior :

If I insert date from my spring boot application and retrieve it, the date entered in db is consistent with the one returned by jpa
but if I try to read date which is not inserted by my application, which is already present in db, then the date returned by jpa is not matching with db value,in fact, its showing me a one hour extra as compared to expected value. This is happening for all the existing date value in that table. (not the one entered by my spring boot application)

Anyone having any clue about this behavior?

My application is created using jhipster 6.4.1 and I observed that there is a DateTimeFormatConfiguration class containing following method

@Override
    public void addFormatters(FormatterRegistry registry) {
        DateTimeFormatterRegistrar registrar = new DateTimeFormatterRegistrar();
        registrar.setUseIsoFormat(true);
        registrar.registerFormatters(registry);
    }

Not sure, if this is playing any role.

Are you sure you actually reread the JPA entity again? If you save and load in the same transaction the load just returns the instance just saved without accessing the database. — Jens Schauder
I highly recommend using Instant for storing dates or timestamps in the database. — Jens Schauder
Can you please go to the oracle db and execute "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';" and then select the timestamps from table and check the timezone? — Simon Martinelli

user2477173 user2477173 · Accepted Answer · 2020-05-02T08:54:59

Thanks everyone for the answers. I have found the issue. There was no issue with the code/spring data jpa/oracle/jhipster. The problem was with my calculation.

The date which I felt was not working as per my expectation was this: 11-01-2017 09:36:34

This utc date is 11th of Jan 2020, If I convert this to AEST, it comes as +11hours because it was during day light saving. So my application was showing me correct date time which is 2017-01-11T20:36:34.

The date which I felt was working fine, is this one : 28-04-2020 00:32:02 This utc date 28 April 2020 where there is no day light saving, if I want to convert this to AEST, then I should add +10 hours.

Basically I was comparing the wrong dates, one was during day light saving and other was after day light saving ends and which is why , I was getting different results and I thought my application is failing somewhere to give me consistent datetimes.but thats not true, I was applying wrong calculations.

Spring data jpa returning inconsistent values of Date from oracle database table

1 Answers