Time Zone

What is the syntax for me to see the time zone in Oracle 10g?
donvike1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
In what format?

There is:
select dbtimezone from dual;
0
anumosesCommented:
select sessiontimezone from dual
0
anumosesCommented:
You can select both in one query and see what is your requirement.

select dbtimezone,sessiontimezone from dual;
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.