Hot questions for Using JasperReports in timezone

Question:

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?


Answer:

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.

Question:

I have a timezone-delicate report in Jasper and I can't really seem to figure out how to show a few dates relative to a timezone.

I have a view which returns dates with the following format:

"2015-03-02 11:45:00+01"

"2015-03-02 23:59:59+01"

"2015-03-03 00:00:00+01"

"2015-03-03 08:00:00+01"

"2015-03-03 09:20:00+01"

"2015-03-03 11:00:00+01"

"2015-03-03 09:00:00+01"

"2015-03-03 09:30:00+01"

etc (notice the +01 at the end)

In my report, I have:

new SimpleDateFormat("HH:mm", $P{REPORT_LOCALE}).format($F{start_date});

However, for example, for "2015-03-02 11:45:00+01" I don't get 12:45 shown, I get 11:45.

Also, I need to sum-up the hours (they're intervals) and this gives me a 1hr (in this case) error.

Can anyone help me show the correct hour?

Thanks!


Answer:

SimpleDateFormat takes a Date, not a Calendar - which means it can't be provided the time zone in the value itself.

Assuming you need to stick with SimpleDateFormat (rather than using Joda Time or Java 8's java.time, for example) then you'll need to set the time zone on the SimpleDateFormat itself. If you need to take the time zone from the data (rather than having a report-wide zone) then you'll need to call setTimeZone before formatting each value - but of course, you'll also need to make sure you've got the time zone in the value, and java.sql.Timestamp doesn't have any notion of a time zone, as far as I'm aware.