convert the GMT time on the report/universe to PST time

system · October 29, 2010, 3:34pm

Hello all,
Really appreciate any help on this issue.
My reports are giving output on GMT based off the server machine configuration. I need to create a variable at the report level to covert the time to PST
for eg. if its sep 8, 2010 05:20:00 PM i want the create a variable to convert this to PST and show it as Sep 8, 2010 10:20:00 AM (-7 hrs).
Or need some help to make a object at the universe itself.

sanappi (BOB member since 2010-05-03)

system · October 30, 2010, 4:50am

What if you change the current time zone in user’s infoview preferences?

Thanks,
Zaif

zaif235 (BOB member since 2010-06-15)

system · October 30, 2010, 5:04am

Thanks

Nisha Kothari (BOB member since 2009-10-16)

system · October 30, 2010, 3:22pm

Hello Zaif, in infoview it is showing as PST itself…

sanappi (BOB member since 2010-05-03)

system · November 1, 2010, 4:06am

Hello can anyone please help me on this… 85 views and cannot believe only 2 replies… i am really in need of this solution…

sanappi (BOB member since 2010-05-03)

system · November 1, 2010, 1:25pm

Can you tell me the DB?
If it is oracle you can create universe level variable with

tz_offset( timezone )

or
using


FROM_TZ( timestamp_value, time_zone_value )

Regards
Bilahari

billu (BOB member since 2009-11-09)

system · November 2, 2010, 3:35am

thanks for reply Billu,
Our database is Oracle 10G. Have a question

tz_offset( timezone )

so for timezone should i give the time zone that i need like in my case it is PST. But what about the timestamp column and how does this work without pointing to any column?

FROM_TZ( timestamp_value, time_zone_value )
In here timestamp is the column that has GMT time while time_zone is the “PST” right?

Please assist.[/i][/b]

sanappi (BOB member since 2010-05-03)

system · November 2, 2010, 9:18am

Hi sanappi,

You are right. You should supply timezone code corresponding to PST which you can get by running the query

SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES where upper(tzabbrev)='PST'

In the universe you can create an object without pointing to any column. Though it would show error on validation,it wont give you any error while running the report as it would be used along with other objects which are taken from database fields.

Alternatively you can insert oracle view sys.DUAL in to the universe and link to that table(Click the ‘Table Button’ object defnition tab).

from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

If you executed the following SQL statement:

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00')
from dual;

You would now get the following result:

11-Sep-05 01.50.42.000000 AM +05:00

So this function does not satisfy your requirement i guess…

Regards
Bilahari

billu (BOB member since 2009-11-09)

system · November 3, 2010, 1:57am

Hello Billu

So this function does not satisfy your requirement i guess…

first of all thanks for you reply again and clarify certain things, these are definitely very good suggestions which i will be using at the database level.
But yah i am looking for a solution at the report level.

sanappi (BOB member since 2010-05-03)

system · November 3, 2010, 11:44am

hi sanappi,

Creating tz_offset( timezone ) in universe and using that at report level should work for your requiremnt .
FROM_TZ( timestamp_value, time_zone_value ) is not a solution for this as its different functionality.

If you want to resolve it at the report level,we need to try something else.

One doubt , if its always GMT/PST offset ,then why cant we create a formula which subtracts the offset(Ofsset value can be hardcode if it is always GMT to PST) from date field.

billu (BOB member since 2009-11-09)

system · January 15, 2011, 2:21pm

Hello gurus, i am still having problem with this rek. I am not able to find a syntax that would help me resolve the issue. I need to resolve this on the same field that holds all the time values.
Problem again “All the predefined audit reports (crystal reports) are showing the timestamp on GMT time. The rek is to keep all the existing format on the reports but have them display the proper time which is PST in our case. So any help is very much appreciated”
BOXI 3.1 sp2
backend Oracle 10g
field that has time stamp : start_timestamp
table name: Audit_event.

sanappi (BOB member since 2010-05-03)

system · January 16, 2011, 1:58am

@sanappi:

Did you try what billu suggested in his last post ? If it always GMT to PST, just subtract the hours from the object in the report.

BO_Chief (BOB member since 2004-06-06)

system · January 16, 2011, 7:28am

I did try that but the rek is that i find a solution on Universe since there are lot of predefined reports as well adhoc running.

sanappi (BOB member since 2010-05-03)

system · July 26, 2011, 6:00pm

How we can change timzone from GMT to PST for DB2 database in Universe? Please advice

BOKris (BOB member since 2003-08-27)

system · October 23, 2012, 11:08am

What is the syntax in MS SQL Server 2008 for Time Zones?

vasu237 (BOB member since 2007-02-19)

MarkP · October 23, 2012, 11:39am

I had to build a database function - Google is your friend in this instance.

system · October 23, 2012, 6:02pm

I have searched in Google but unfortunately I did not get any syntax … [/b]

vasu237 (BOB member since 2007-02-19)