Adjusting Date/Time for timezone

jballnikjballnik Junior Member
edited December 31, 1969 in Designing Analytics Reports
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

Comments

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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?

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog

    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • jballnikjballnik Junior Member
    edited December 31, 1969
    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??
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog

    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog

    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • jballnikjballnik Junior Member
    edited December 31, 1969
    That's great Michael..I will try these out and let you know

    Thanks a mil!!!
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    John,

    Did one of these solutions work for you?

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog

    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • Hi,


     


    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.


     


    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.


    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.


     


    Thanks


  • Do the dates in the database have a timezone associated with them?


     


    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.


    Regards,

     

    Jesse Freeman

    Solution Consultant

    OpenText Corporation

    http://www.actuate.com | http://www.opentext.com

     

  • 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.  


Sign In or Register to comment.