Time Zone

What is the syntax for me to see the time zone in Oracle 10g?
donvike1Asked:
Who is Participating?
 
anumosesCommented:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm

In the above look for this, which might help. You might have to change cities based on your requirement


SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
     'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')
     AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
FROM DUAL;
0
 
slightwv (䄆 Netminder) Commented:
In what format?

There is:
select dbtimezone from dual;
0
 
anumosesCommented:
select sessiontimezone from dual
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesCommented:
You can select both in one query and see what is your requirement.

select dbtimezone,sessiontimezone from dual;
0
 
donvike1Author Commented:
I used both solutions and came up with different times - I'm located in Atlanta Georgia, Eastern time how do I find Eastern timezone as opposed the the number?

SQL> select dbtimezone from dual;

DBTIME
------
-05:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-04:00

SQL>
0
 
anumosesCommented:
http://toolkit.rdbms-insight.com/tz.php

In the above link look for this

SELECT to_char(sysTIMESTAMP,'HH24:MI:SS.FF')
     ,to_char(LOCALTIMESTAMP,'HH24:MI:SS.FF')
     ,to_char(current_TIMESTAMP,'HH24:MI:SS.FF')
     FROM dual;
0
 
donvike1Author Commented:
I would like to see Eastern, Pacific etc.
0
 
slightwv (䄆 Netminder) Commented:
Just to add:

select extract(timezone_region from systimestamp)  from dual;


This likely returns UNKOWN because:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions050.htm#i1017161

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.

You will likely need some starting point referenced by anumoses
or
specify the regional timezone for the database.

0
 
donvike1Author Commented:
OK, let me explain this

My database wont open because of the Daylight saving change.

My database is on US/Pacific Time and I live in US/Eastern.  I'm trying to change my timezone to US/Eastern - what am I doing wrong.  See below:

[oracle@longhorns ~]$ emctl resetTZ agent
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
Updating /opt/app/oracle/product/10.2.0/localhost.localdomain_movies/sysman/config/emd.properties...
resetTZ failed.
The agentTZRegion in:
/opt/app/oracle/product/10.2.0/localhost.localdomain_movies/sysman/config/emd.properties
is not in agreement with what the agent thinks it should be.
Fix your environment.
Pick a TZ value that corresponds to time zone settings listed in:
/opt/app/oracle/product/10.2.0/sysman/admin/supportedtzs.lst
[oracle@longhorns ~]$ US/Pacific
-bash: US/Pacific: No such file or directory
[oracle@longhorns ~]$ TZ set to US/Pacific
-bash: TZ: command not found
0
 
slightwv (䄆 Netminder) Commented:
>>My database wont open because of the Daylight saving change

That is a different question than the one asked.

>>/opt/app/oracle/product/10.2.0/sysman/admin/supportedtzs.lst

What timezones are in that file?

0
 
donvike1Author Commented:
Thank you anumoses, this works - now how do I keep my database at this time so that I can open it?

SQL> SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
  2  'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')
  3  AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
  4  FROM DUAL;

West Coast Time
---------------------------------------------------------------------------
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
0
 
donvike1Author Commented:
Sorry, let me rephrase that - how do I reset my database for America/Los_Angeles?  Right now it is reading as US/Eastern and I want it to continue as America/Los_Angeles.  Thank you so much for your assistance.
0
 
slightwv (䄆 Netminder) Commented:
>>Thank you anumoses, this works - now how do I keep my database at this time so that I can open it?
>>how do I reset my database for America/Los_Angeles?  

Again.  Different question than what was asked.  You should probably close the one asked by accepting the posts that helped you "to see the time zone in Oracle 10g" and open a new one for how to change it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.