Best practice passing date/timestamp as parameter using JasperReports Library

[c1247847]

Hi!

I am using the JasperReports Library (not JasperReports Server) to integrate Reports in my web application.

The reports use SQL queries to get the data from an Oracle database. Point in times in the database tables are stored using the column type TIMESTAMP WITH TIMEZONE. The reports are created using Jaspersoft Studio, but this should not matter.

I have a report where the user can set a date range (from and to). So these values must be parameters in the SQL of the report. What is the best practice to pass this information using parameters.

I found several possibilities:

1. Use a parameter of type java.sql.date
2. Use a parameter of type java.sql.timestamp
3. Use a parameter of type java.util.date

All of them do not contain the Timezone information, so what exactly happens if I do something like this in the SQL query: creation_date - dateFrom, whereas creation_date is of type TIMESTAMP WITH TIMEZONE and dateFrom is the parameter from outside.

The dateFrom parameter needs to be interpreted using a TimeZone for the calculation or am I wrong? Which TimeZone is used then?

Furthermore the dateFrom parameter must somehow respect the TimeZone of the user which creates the report.

 

Another 4. possibility is to pass the dateFrom as a String including the TimeZone information and to parse it in the SQL query to a TIMESTAMP WITH TIMEZONE. This would nicely solve any timezone issues, but is it recommended?

If I want to display the dates which have the column type TIMESTAMP WITH TIMEZONE in the report in the TimeZone of the current user can I do this in the .jrxml file or do I have to prepare this in the SQL query itself? Does JasperReports Library support the type TIMESTAMP WITH TIMEZONE?

[armanbhansali9821]

got any ans for this?