Group by Month in timezone

0

Could someone please tell me how to correctly group by Month while allowing for multiple timezones in Jaspersoft?

Our Mysql database is in UTC time, and I am in Australia, UTC +10:00, so a date that is '1st May 2015 08:00' for me is '30th April 2015 22:00' in the database. Using patterns in the designer converts UTC dates to my timezone correctly, but I cannot use one of these inbuild patterns in a group expression (at least not to my knowledge). How can I group by month while taking into account the timezone?

Thanks,

Justin

UPDATE, I figured it out.

Create a parameter (E.g. $P{DateFormatter}, not for prompting, class of DateFormat) in the report with the following default expression:

$P{REPORT_FORMAT_FACTORY}.createDateFormat("dd/MM/yy", $P{REPORT_LOCALE},$P{REPORT_TIME_ZONE})

This could be any date format, such as "MMMM, yyyy", etc etc.

Then in the Group Expression its as simple as writing $P{DateFormatter}.format($F{TheDate}). This will then correctly group according to the local timezone, as opposed to the SQL database timezone.

jhickling's picture
Joined: Jun 26 2015 - 9:54pm
Last seen: 7 months 2 days ago

0 Answers:

No answers yet
Feedback