How to display an oracle timestamp with time zone field? Nothing seems to work. [noob]

[jlittle2]

Does anyone have an example report, or XML snippet, which allows the report to show a date/time?

googling "jasperreport timestamptz"  or "jasperreport dateValue" or "jasperreport timestampvalue"  finds nothing - zero documentation.

The problem:

Let table A have a column "startedat" which is a defined as TIMESTAMP(6) WITH TIME ZONE.  This is pretty much the only way to correctly manage dates & times form different timezones in oracle.

When this value is selected in a report, e..g using the query:

Select startedat from A

In sqlDeveloper or Toad, it displays: 28/02/2011 23:24:34.278630 +00:00

When This query is added as the only query in a report, then the "startedat" field is dragged into the report detail area, it generates this:

   <textFieldExpression class="java.lang.String"><![CDATA[""+$F{STARTEDAT}]]></textFieldExpression>

 And the output on the report is just this:

oracle.sql.TIMESTAMPTZ@123a2a4

 I tried every combination of method and type i could find.  Some forums suggested this:

                <textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{STARTEDAT}.timestampValue()]]></textFieldExpression>

 But this just gives "error evaluating expression", even though it was formed with the expression builder.

 The underlying type that iReport seems to be giving to the oracle timestamp is oracle.sql.timestamtz, which I cand find any documentation for in relation to jasperreports. 

What I dont undestand is why ireprot cant just automatically convert it to a java date and format it automatically.  the report has date formats if you right click on the date field in the report and select "field Patter", but these have no visiable effect.

I tried to convert it to a java.sql.Date using .dateValue(), but this wont compile either - I cant see any way to interact with the date fields. 

Obvoisly, I could Use oracle TO_CHAR and format the date as a string, but this will use the timezone of the DB, not the report viewer.

 

[jlittle2]

I also read "JasperReports for Java Developers" book, and it does not mention dates, or even have any examples on how to display anything except strings, or do any display conversion. I cant find any documentation what so ever. There are some tutorials, but None seem to have Dates, only strings and numbers.

[jlittle2]

Trial and error has yielded the display of a date:

<textFieldExpression class="java.lang.String"><![CDATA[""+$F{STARTEDAT}.dateValue( $P{REPORT_CONNECTION} )]]></textFieldExpression>

Next problem is how to format it. There dont seem to be any examples out there. If I could write arbitrary java code, I might find a way extract the Oracle date, time and timezone, and then convert it into a java standard date, time and timezone, pulling in the reports local, accounting for the time zone differences, then formatting it, but to do that in a one line expression is going to be tricky.

Post Edited by jlittle2 at 03/16/2011 21:19

[jlittle2]

Found the answer:

 

<textFieldExpression class="java.lang.String"><![CDATA[""+new SimpleDateFormat( "dd/MM/yy HH:mm:ss Z", $P{REPORT_LOCALE} ).format( $F{STARTEDAT}.dateValue( $P{REPORT_CONNECTION} ))]]></textFieldExpression>

 

posted it on http://devhobbs.blogspot.com/2011/03/how-to-display-oracle-timestamp-with.html