Java, Oracle db - Timezone issues after trunc(nvl) strips timestamp from a datetime object

0
votes

Trying to fix a bug in our reporting. Currently the issue is as stands:

At 9:45PM on 2/22 in PST someone submits a work order.

It hits our Oracle Database and normalizes to EST (our db is in EST, but we work with clients all over US).

In iReport, we are using the following:

trunc(nvl(ls.date_occurred,ls.date_created)) between TRUNC($P{DATE_FROM}) AND TRUNC($P{DATE_TO})

This STRIPS the timestamp off of the datetime object, so when the report is generated it does not save the hours, only the date which is now 2/23 (at 12:25 AM respectively).

This obviously throws off our reporting feature. All of the data seems to be correct except this date offset that is generated a day after because of the timezone difference, and the adjusted data not having a timestamp asociated with it. Does anyone have another way to adjust for datetime without using a function that strips the timestamp off of the date?

2
javaoracletimezonetimestampjasper-reports
So, why do you wrap the left-hand side within TRUNC()? It seems all you need to do is to ditch it. - mathguy
Do you mean: (nvl(ls.date_occurred,ls.date_created)) between TRUNC($P{DATE_FROM}) AND TRUNC($P{DATE_TO}) - OhMyGodAGirl
Yes. And you don't need the outer parentheses around the nvl(...) - they are left from the TRUNC function. - mathguy

2 Answers

0
votes

As I understand your from/to dates are not in EST which makes the discrepancy between the dates you require in your report to the date in you Database. In order to get the correct records instead if truncating the dates you need to adjust the requested to/from dates according to the timezone of the request (If you request from PST timezone first convert the dates to EST then make the query)

also, you can look at : TIMESTAMP WITH TIME ZONE Datatype https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

0
votes

Turns out that I need to adjust for the timezone of date_created inside the nvl, because date_occurred is trunced whereas date_created is not. This causes data loss.