Datetimes are printed in UTC

Asked by Martin Collins

In 6.1 OpenERP changed datetimes to be stored in UTC timezone. I am in America/Belize timezone (UTC -6). All my jasper_reports now print in UTC. Shouldn't jasper_reports pick up the user's timezone and convert, or do I have to modify my reports to do it? If the latter, how?

Question information

Language:
English Edit question
Status:
Open
For:
openobject-jasper-reports Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Martin Collins (mkc-steadfast) said :
#1

By the way, my reports all use SQL queries, not XML. It just occurred to me that that might be germane.

Revision history for this message
Martin Collins (mkc-steadfast) said :
#2

Yes, if I change my queries so instead of just
  sometime_datetime
I have
  (sometime_datetime at time zone 'UTC')::timestamp as sometime_datetime
then I get the correct datetime in my reports.
If I don't cast it to timestamp it gets converted back to UTC, and if I don't alias it the field is just called 'timezone'.
So, unless someone offers a more elegant solution, I'll be spending tomorrow going through all my reports this way.

Revision history for this message
Martin Collins (mkc-steadfast) said :
#3

I have found the more elegant solution, but it requires a small patch to jasper_reports.

Reports have a built-in parameter REPORT_TIME_ZONE and if it is set to the timezone you want then fields with an Expression Class of java.sql.TimeStamp or java.util.Date (possibly others) will be converted automatically. No need for the SQL hack above.

Dates in string form (such as those passed in as parameters) can be converted with a Text Field Expression like so:
   (new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm")).parse($P{StartDateTime})
and an Expression Class of java.util.Date.

The snag is that REPORT_TIME_ZONE must be a java.util.TimeZone so you cannot just pass a timezone string parameter from OpenERP. This patch allows you to do that:

--- jasper_reports/java/com/nantic/jasperreports/JasperServer.java 2012-03-04 01:25:44 +0000
+++ jasper_reports/java/com/nantic/jasperreports/JasperServer.java 2012-10-12 17:54:36 +0000
@@ -142,6 +142,10 @@
         if ( index != -1 )
             parameters.put( "SUBREPORT_DIR", jrxmlPath.substring( 0, index+1 ) );

+ // Use the user's time zone
+ if ( parameters.containsKey( "report_time_zone" ) )
+ parameters.put( "REPORT_TIME_ZONE", java.util.TimeZone.getTimeZone( parameters.get( "report_time_zone" ).toString() ) );
+
         // Declare it outside the parameters loop because we'll use it when we will create the data source.
         Translator translator = null;

You can then get your reports in the user's timezone by adding something like this:

        user_context = pooler.get_pool(cr.dbname).get('res.users').context_get(cr, uid, context)
        params['parameters']['report_time_zone'] = user_context.get('tz') or 'America/Belize'

to your report's parser function.

I'm changing this to a bug report in the hope that a committer will add the patch.

Can you help with this problem?

Provide an answer of your own, or ask Martin Collins for more information if necessary.

To post a message you must log in.