Converting date from GMT to EST in BOBJ 4.1

building reports webintelligence
1

Hey Community!!

Our Audit universe seems to be defaulted to GMT timezone. After creating a report for failed instances the “EventDateStamp” field shows the failure time +4 hours.

How can I convert this in the webi report to show EST? Or if it makes more sense to do it at the universe level, how can I go about that?

Thanks in Advance!! :hb:

MHassan (BOB member since 2013-11-19)

2

In your universe you could create a dimension with SQL similar to this:

DATEADD(hh,-5,AUDIT_EVENT.EvenTimestamp)

richardcottave (BOB member since 2006-03-30)

3

Thanks for your help!

Until I get the software installed on my machine (another week or so) is there a way this can be done at the report level using the [EventDateStamp] field, or any other function in webi?

Thanks

MHassan (BOB member since 2013-11-19)

4

You can do this at the report level by using custom sql. You must edit the query and select custom sql.

richardcottave (BOB member since 2006-03-30)

5

I will give that a try but I do believe there is way it can be done in the report itself as well.

Thanks for your help!

MHassan (BOB member since 2013-11-19)

6

This is very hack-ish since it adjusts the time by a specific number of hours and doesn’t take DST into consideration. But it should suit your short term need:

=RelativeDate([Event Date/Time];-5/24)

Which RDBMS are your audit tables on? Ours is Oracle, and I created a function in the database to convert times from GMT to ET:

CREATE OR REPLACE FUNCTION fix_timestamp (i_date DATE)
RETURN date
IS
BEGIN
    return cast(from_tz(cast(i_date AS TIMESTAMP),'GMT') at time zone 'US/Eastern' as date); 
END;

Even if you can’t create database-based functions, you can use the CAST in a universe object.

Joe

joepeters :us: (BOB member since 2002-08-29)