[Oracle11g]Check DB Timezone and Session Timezone

Use the following SQL Queries to check the Database Timezone and Session Timezone values.

Database Timezone

The database timezone function returns the timezone specified when Database was installed or the CREATE DATABASE script was executed .Output would be a value like -08:00  or +5:30 etc

SELECT DBTIMEZONE FROM DUAL;

Session Timezone 

Returns the timezone of the current session, Output would be a value like -08:00  or +5:30 etc

SELECT SESSIONTIMEZONE FROM DUAL;

Difference in Date/Timestamp between Siebel and OBIEE

Here is a very Interesting bug that was raised in one of the projects we had worked for. We had to display a Date/Time field in OBIEE. The corresponding records in Siebel application was showing a different Date and TimeStamp.  

Debugging

1. First we checked whether the Date column value from OLTP(Siebel) Database and OBIEE are matching. Upon checking we could see that the Date and Timestamp was matching between Database and OBIEE. 
2. Next task was to identify why this difference between DB column and Siebel UI. To do this we check the Default Timezone set in Siebel UI. This was set to GMT -8:00 (US/Canada).
3. Created a new record in UI and checked the DB column value, we could see that when we created a record in UI with UTC -8:00(US/Canada Timezone) then the corresponding record in UI was created with 8 Hours added to it so as to make it UTC(GMT) time. I.e for example if i created a record with time 3/8/2015 12:00 AM GMT -8:00 then the Date column in the DB would stamp UTC time which is 3/8/2015 8:00 AM GMT.
4. One way to sync date time values in OBIEE and Siebel is to make a difference of 8 Hours but considering Daylight saving time is a challenge here.

Update: Oracle New Time function can be used to convert a date time value from one Timezone. Checking whether Daylight saving can be considered here. Will keep you guys posted.