Timezone in Input Control and Reports

[AndreC]

Hello,

I am having some issues with the timezones.

Here is the situation;

The database is hosted on AWS and is set to UTC. The Jasperserver is also running on AWS and system time is also UTC.

At login to JasperServer I do specify the timezne using the drop down list (timezoneIds).

I have then created an Input Control that make a database query filtering on dates. The dates could be a range or 'today' which translate to CURRENT_DATE, or 'yesterday' ...

If I work in UTC time than it seem to all work.

If I select a different timezone then things gets mixed.

In the debug logs (jasperserver.log)

2014-01-16 02:28:03,090 DEBUG JRJdbcQueryExecuter,pool-4-thread-2:396 - Parameter #2 (dateRange of type java.lang.String): today

2014-01-16 02:28:03,090 DEBUG JRJdbcQueryExecuter,pool-4-thread-2:396 - Parameter #3 (startDateStr of type java.lang.String): 2014-01-16 02:27 EST

2014-01-16 02:28:03,091 DEBUG JRJdbcQueryExecuter,pool-4-thread-2:396 - Parameter #4 (dateRange of type java.lang.String): today

2014-01-16 02:28:03,091 DEBUG JRJdbcQueryExecuter,pool-4-thread-2:396 - Parameter #5 (endDateStr of type java.lang.String): 2014-01-16 02:27 EST

 

The problem is that the user expect that the dates he entered are local to whatever timezone he specified at login, which is  EST, what we see here is the date get converted but then the time become wrong as it has the wrong timezone associated to the new time it seem.

 

I have tried to also set the timezone to UTC in the datasource (JDBC with oracle.jdbc.OracleDriver) and still does not seem to match

 

 

Basically how to configure the server such that international clients can connect using their local timezone and have all date converted properly?

[AndreC]

While there seem to be some sort of convertions for the date specified in the control as date type, how to tell oracle that I want the "current_time" or "sysdate" to apply the user specified timezone?

so if the user is in EST and specify 'today' that the system convert the 00:00 to 05:00 such that the query filter on the converted UTC time as the database is set.

 

I'know i am confusing but im not sure how to explain..

[mastanraosegu]

I am also facing problem Waiting for response.

[mastanraosegu]

Can you explain brefly

[Mahendran.Balasubramanium]

I am looking for solution for this problem. Please let me know

[Mahendran.Balasubramanium]

Do we have any resolution for this? I need the solution as well