RE: How to get timezone in 9i

From: Hollis, Les <Les.Hollis_at_ps.net>
Date: Mon, 21 Feb 2005 19:56:44 -0600
Message-ID: <FCC960FDB92F5E469A02464FF72872F404262793@pscdalpexch50.perotsystems.net>

I had to change the dbtimezone on one of my 9i DB's about 3 hours ago. I did

Alter database set time_zone=3D'America/New_York';

To get it to show when doing=20

Select dbtimezone from dual;

I had to reboot the database...only then it actually change from America/Chicago to America/New_York

Select * from v$timezone_names where tzname like 'America/%'; will give you all of the timezones and their abbreviations

select * from v$timezone_names where tzname =3D 'America/New_York'

TZNAME                    TZABBREV
------------------------- --------
America/New_York          LMT
America/New_York          EST
America/New_York          EWT
America/New_York          EDT

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ramesh FL Sent: Monday, February 21, 2005 6:04 PM
To: Christian.Antognini_at_trivadis.com
Cc: oracle-l
Subject: Re: How to get timezone in 9i

On Fri, 18 Feb 2005 16:42:34 +0100, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> >I need to get international time zones in 3 letter format, something
> >like EST,CDT ....etc.

>=20

> ALTER DATABASE SET TIME_ZONE =3D3D 'EST';

>=20

> Then when you select DBTIMEZONE you get the correct format.

>=20

> Notice that you cannot alter the database if it stores tables with =3D
> TIMESTAMP WITH LOCAL TIME ZONE columns.
> --

---

---

Hi,

I am using Oracle 9.2 on Win2k Pro.  Here is a cut and paste of SQL
PLus:
 =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
11:43:19 SQL> sho user
USER is "SYS"
11:43:22 SQL>
11:43:25 SQL> select dbtimezone from dual;
more...

DBTIME
------
-07:00

Elapsed: 00:00:00.04
11:43:32 SQL> select sessiontimezone from dual;
more...

SESSIONTIMEZONE
------------------------------------------------------------------------
---
-08:00

Elapsed: 00:00:00.03
11:43:40 SQL>
11:43:41 SQL> ALTER DATABASE SET TIME_ZONE =3D'EST';
ALTER DATABASE SET TIME_ZONE =3D'EST'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

Elapsed: 00:00:00.01
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

1) What is the difference between session time zone and db time zone
and why is there an hour diference. Also, I am in Eastern Time (which
is 5 hrs behind GMT).  Why do I get -7:00 and -8:00 ?

2) ALTER DATABASE SET TIME_ZONE, does not seem to work.

Thanks.
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 21 2005 - 20:57:13 CST