Adjusting Date/Time for timezone

jballnik

Hi Gang,

We have a customer who is in Singapore but the application/database server is in Dallas. Is there is a method or function to convert the date fields to the local timezone on the reports? Right now all the date fields are printing as they are in the database and are not reflecting the time change.

Thanks so much,

John

mwilliams

Hi John,

So, the reports are being run on the servers in Dallas and are running for everyone with Dallas time information no matter where they are?

jballnik

Hi Michael,

The servers including the database are in Dallas but the users are all in Singapore. The Web application that they use is smart enough where it will adjust the time for date fields and display it correctly on the screen. For instance the REPORTDATE field is 8/1/09 08:00:00 AM in the database but displays as 8/2/09 00:00:00 AM in the application...it adjusts the 14 hours.

The report does not do this and when it is run the dates on the BIRT report do not match what is the in the application. They are all off by 14 hours.

Do you know if there is a function or method available to adjust the dates in BIRT??

mwilliams

John,

You could use a BirtDateTime function to adjust the time by the 14 hours needed. If not all of your users are in Singapore, you could set this up on a parameter basis.

The function is BirtDateTime.addHour(row["Date"], int hours)

I'll have to look to see if there's a function that will recognize time zones.

mwilliams

John,

Another thing you could do would be to create a dateFormat for Singapore in your initialize script with script like the following:

importPackage(Packages.java.text);
importPackage(Packages.java.util);
dateFormat = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.MEDIUM);
dateFormat.setTimeZone(TimeZone.getTimeZone("Singapore"));

Then you can call this new format on dates for your report design:

dateFormat.format(dateField)

Hopefully one of the above works for your situation. Let me know if not.

jballnik

That's great Michael..I will try these out and let you know

Thanks a mil!!!

mwilliams

John,

Did one of these solutions work for you?

G-Man

<p>Hi,</p>
<p> </p>
<p>Apologies to start this thread again, but as the issue I am facing is exactly opposite of what John was facing, and upon searching I came across this thread.</p>
<p> </p>
<p>I am using BIRT Designer 4.4.0 where I have created a data object to fetch data from Teradata and I can see dates are getting displayed correctly in preview of dataSet. Even when I designed report in BIRT designer and run it locally I can see dates being displayed correctly.</p>
<p>But When I deploy the report to server and run it, the dates being displayed as Date -1 Day. Upon investigating I found out that it is issue with datetime where on server time is getting adjusted automatically to minus 4:30 hour since Server is based in UK and I am in India. Is there anything I can do to make report display whatever getting fetched from database as it is.</p>
<p> </p>
<p>Thanks</p>

JFreeman

<p>Do the dates in the database have a timezone associated with them?</p>
<p> </p>
<p>You could try creating a computed column and use the existing dates as a base for building a new date object to get around the issue.</p>

G-Man

<p>Yes, dates have timestamp in database itself and I can not remove it as I need it in report for SLA calculation. Even I tried to remove Time for few columns by defining them as only Dates instead on datetime at data set output, but no success as it makes time as 00:00:00 which is again changing on Server. It would be difficult to make computed column for every date column as there  are many. I am just wondering why such behavior, Ideally any reporting tool should display whatever comes from database unless some data manipulation deliberately done or if any setting changed.  </p>