OraFAQ Forum: SQL & PL/SQL » figuring out timezone/region of oracle instance

Home » SQL & PL/SQL » SQL & PL/SQL » figuring out timezone/region of oracle instance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

figuring out timezone/region of oracle instance [message #132706]

Mon, 15 August 2005 16:01

Godfrey T. Degamo
Messages: 12
Registered: July 2005

Junior Member

Hello.

I do the following to figure out the timezone of my database:

select dbtimezone from dual;

DBTIME
------
-07:00

Now what does this describe? I presume this means -7 hours behind something. I presume GMT? I am on US east coast, which is -4 hours behind GMT. So what is -7?

My next question is, what REGION of time is my oracle instance reporting? -7 doesn't tell me if it's EST, or EDT, or MST, or whatever.

Anyways thanks for the help.

Report message to a moderator

Re: figuring out timezone/region of oracle instance [message #132717 is a reply to message #132706]

Mon, 15 August 2005 19:27

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Yes, it is GMT - 7 hours, which is Pacific Daylight Time (PDT) as it is here in Southern California. What you want is GMT - 4 hours (EDT). Time zones (db and session / local) can be set using either an offset from GMT like '-04:00' or using a timezone_name like 'US/Eastern'. Valid time zone names and abbreviations are listed in the v$timezone_names data dictionary view. Please see the demonstration below. Whichever way you define them is how they are displayed and how they must be referenced, in cases where a specific reference is required.

scott@ORA92> SELECT dbtimezone FROM DUAL
  2  /

DBTIME
------
-07:00

scott@ORA92> SELECT TO_CHAR (SYSTIMESTAMP,
  2  		     'DD-MON-YYYY HH24:MI') PDT,
  3  	    TO_CHAR (NEW_TIME (SYSTIMESTAMP, 'PDT', 'EDT'),
  4  		     'DD-MON-YYYY HH24:MI') EDT,
  5  	    TO_CHAR (NEW_TIME (SYSTIMESTAMP, 'PDT', 'GMT'),
  6  		     'DD-MON-YYYY HH24:MI') GMT
  7  FROM   DUAL
  8  /

PDT               EDT               GMT
----------------- ----------------- -----------------
15-AUG-2005 17:19 15-AUG-2005 20:19 16-AUG-2005 00:19

scott@ORA92> SELECT sessiontimezone FROM DUAL
  2  /

SESSIONTIMEZONE
---------------------------------------------------------------------------
-07:00

scott@ORA92> SELECT LOCALTIMESTAMP FROM DUAL
  2  /

LOCALTIMESTAMP
---------------------------------------------------------------------------
15-AUG-2005 17:19

scott@ORA92> COLUMN tzname FORMAT A30
scott@ORA92> COLUMN tzabbrev FORMAT A30
scott@ORA92> SELECT *
  2  FROM   v$timezone_names
  3  WHERE  tzabbrev = 'PDT'
  4  /

TZNAME                         TZABBREV
------------------------------ ------------------------------
America/Ensenada               PDT
America/Los_Angeles            PDT
America/Tijuana                PDT
America/Vancouver              PDT
America/Whitehorse             PDT
Canada/Pacific                 PDT
Canada/Yukon                   PDT
Mexico/BajaNorte               PDT
PST                            PDT
PST8PDT                        PDT
US/Pacific                     PDT
US/Pacific-New                 PDT

12 rows selected.

scott@ORA92> ALTER SESSION SET time_zone = 'US/Pacific'
  2  /

Session altered.

scott@ORA92> SELECT sessiontimezone FROM DUAL
  2  /

SESSIONTIMEZONE
---------------------------------------------------------------------------
US/Pacific

scott@ORA92> SELECT LOCALTIMESTAMP FROM DUAL
  2  /

LOCALTIMESTAMP
---------------------------------------------------------------------------
15-AUG-2005 17:19

scott@ORA92> ALTER SESSION SET time_zone = '-4:00'
  2  /

Session altered.

scott@ORA92> SELECT sessiontimezone FROM DUAL
  2  /

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

scott@ORA92> SELECT LOCALTIMESTAMP FROM DUAL
  2  /

LOCALTIMESTAMP
---------------------------------------------------------------------------
15-AUG-2005 20:19

scott@ORA92> COLUMN tzname FORMAT A30
scott@ORA92> COLUMN tzabbrev FORMAT A30
scott@ORA92> SELECT *
  2  FROM   v$timezone_names
  3  WHERE  tzabbrev = 'EDT'
  4  /

TZNAME                         TZABBREV
------------------------------ ------------------------------
America/Detroit                EDT
America/Fort_Wayne             EDT
America/Indiana/Indianapolis   EDT
America/Indianapolis           EDT
America/Jamaica                EDT
America/Montreal               EDT
America/New_York               EDT
Canada/Eastern                 EDT
EST                            EDT
EST5EDT                        EDT
Jamaica                        EDT
US/East-Indiana                EDT
US/Eastern                     EDT
US/Michigan                    EDT

14 rows selected.

scott@ORA92> ALTER SESSION SET time_zone = 'US/Eastern'
  2  /

Session altered.

scott@ORA92> SELECT sessiontimezone FROM DUAL
  2  /

SESSIONTIMEZONE
---------------------------------------------------------------------------
US/Eastern

scott@ORA92> SELECT LOCALTIMESTAMP FROM DUAL
  2  /

LOCALTIMESTAMP
---------------------------------------------------------------------------
15-AUG-2005 20:19

scott@ORA92>

Report message to a moderator

Previous Topic:	How does an indexes is used against a Table?
Next Topic:	date problem (order by)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 06:17:10 CDT 2025