Dynamic timezone conversion

building semantic layer
1

Hi,

My client uses a CRM tool globally. Within 3 main centres (Europe, North America and Australia), users complete date/time fields using their local time. When this data is saved to the underlying database, all date/time fields are converted into UTC timezone.

Therefore, when users run Webi reports, the date/times returned in the report show as the UTC date/time, rather than what they entered. They want to see the date/time in their local timezone.

Using the users locale settings is not an option. This needs to be solved within the universe.

Ideally I would like to create the date/time objects in a way that when used in a report, would prompt the user as to which timezone to use and a conversion happens. Is this possible??

I am using BO XIR2 on an Oracle 9i database.

Any help would be appreciated.

Darren

dfewell85 (BOB member since 2013-03-07)

2

Interesting approach. I hadn’t thought of universe prompt for that problem, but it should work.

I did my testing in Oracle 11; I believe that this should work in 9i, but I’m not 100% certain.

Oracle has a table that contains all time zones – SYS.GV$TIMEZONE_NAMES. Add this to your universe, and create an object for GV$TIMEZONE_NAMES.TZNAME. It’s only used for the prompt, so it can be hidden. You can adjust the LOV query for this object to reduce the number of time zones that the user can select from.

For each date field that you want to adjust by time zone, create an object like:

CAST((FROM_TZ(CAST(table.datefield AS TIMESTAMP),'+00:00') AT TIME ZONE @Prompt('Select Time Zone','A','Class1\Tzname',MONO,CONSTRAINED)) AS DATE)

Replace table.datefield with the name of the actual column, and Class1\Tzname with the class and name of the object associated with gv$timezone_names.tzname.

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

3

Hi Joe,

Sorry for the delay in replying to this, but i’ve only just had the chance to test this.

I have added the table (it’s actually a view within our DB) and created the objects as you suggested.

However, when i attempt to run a report i get an error:

“A database error occured. The database error test is:
ORA-01878: specified field not found in datetime or interval
.(WIS 10901)”

My column is stored as DATE which also displays the time.

Any ideas?

dfewell85 (BOB member since 2013-03-07)

4

According to what I’ve found, that error occurs when you attempt to convert to or from a time that doesn’t exist in the selected timezone. For example, 3/10/13 2:30am doesn’t exist in us/eastern, so attempting to convert from this date to any other time zone would cause the error.

Can you identify a particular source date/time and target time zone that produce the error?

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

5

Do you have to support Day light Saving ?

jmcnamara (BOB member since 2013-11-12)

6

I have actually managed to resolve this error.

I was trying to be clever concatinating the tzname and tzabbrev columns into one object to provide the ability to select the unique values rather than simply “Australia/Melbourne” for example.

Changing the object to just the tzname removed the error.

It all seems to be working correctly now.

Many thanks for your help with this Joe

dfewell85 (BOB member since 2013-03-07)