Need to Extract Time Zone: Important!!

[rasprer]

Fellows,

Is there simple way to extract the TimeZone(EST, PST) value from 'Select dbtimezone from dual'.

My current format setting reports: -5:00 (see below)

select dbtimezone from dual;

DBTIME
------
-05:00

I understand that I can set this Parameter to 'PST' on the init.ora file, but I would rather not.

Is there anyway to extact or convert this value to return 'PST'.

Please assist

 

[ddrillich]

Let's try:

select extract(timezone_abbr from systimestamp) from dual;
and
select extract(timezone_region from systimestamp) from dual;

Regards,
Dan

 

[rasprer]

Dan..Thanks so much for responding. This is really a very critical issue. I hope your query is my solution.

I just tried the above query against an 8i database..and I get an error..I am hoping the error is related to my Oracle version at home. I will need to try this against an 9i db. Below is the error I recieve when executed.

select extract(timezone_region from systimestamp) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis

 

[ddrillich]

Hi rasprer,

The extract function was introduced around 8.1.6.

Good Luck,
Dan

 

[rasprer]

Weird... Why do I get the error? Does the query work for you, Dan?

 

[ddrillich]

timezone_region, new date time data types and functions, such as systimestamp, were introduced in 9i. systimestamp returns a new data type 'timestamp with time zone'. btw, it also returns a fractional second with six digits of precision.

I'm running this sql on 9.2.0.4.

-- Dan

 

[rasprer]

Dan...this explains why I get the error. Would you mind pasting your results here.

 

[ddrillich]

select extract(timezone_abbr from systimestamp) from dual;
returns UNK

select extract(timezone_region from systimestamp) from dual
returns UNKNOWN

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#SQLRF00639

explains the UNKNOWN value; the explanation however doesn't seem to apply to our case:

The following example results in ambiguity, so Oracle returns 'UNKNOWN':

SELECT EXTRACT(TIMEZONE_REGION
FROM TIMESTAMP '1999-01-01 10:00:00 -08:00')
FROM DUAL;

EXTRACT(TIMEZONE_REGIONFROMTIMESTAMP'1999-01-0110:00:00-08:00')
----------------------------------------------------------------
UNKNOWN


The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region.

-- Dan

 

[ddrillich]

Rasprer,

It's not clear from your original post whether you are aware of the fact that the following is also available to you:

alter database set time_zone='US/Central';

Shutdown
Startup

select dbtimezone from dual;

Returns US/Central

-- Dan

 

[rasprer]

Dan..Thanks for the followup. I am aware with the 'alter database set time_zone' command, but another program is utilizing this value, and I cannot update this value. Our current dbtimezone value is set to -5:00.

I merely want to display 'PST', 'EST', etc by reading or converting the current dbtimezone of -5:00.

I hope this clears it up..at least a little.

 

[ddrillich]

So, what about?:

select
case dbtimezone
when '-05:00' then 'EST'
more when clauses ....
end
from dual

 

[rasprer]

DDrillich...I thought about using the Decode and/or your case statement already. The issue would be the 'hardcoded' value of 'EST', 'PST', etc. This is what we are doing right now to continue our testing, but this is NOT an option once we deliver the source to our clients.

Thanks for you input.

 

[ddrillich]

I would guess that there is a system view which holds the time displacements, but I can't find it; V$TIMEZONE_NAMES doesn't have it.

 

[rasprer]

ddrillich...I too found an article referring to V$TIMEZONE_NAMES and I too can't find it. Does anyone know if this exists.

 

[ddrillich]

rasprer,

I tried to say that I can see the V$TIMEZONE_NAMES view but it doesn't hold the time displacements and I wonder if there is another related view with this information.

Doesn't 'select * from all_views' list the V$TIMEZONE_NAMES view?

It is owned by SYS. So, maybe you don't have the privilege to see it.

 

[rasprer]

DDrillich...I am able to see the view. I too do not see time displacements.