Re: 10gR2 - quick Time Zone question

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 5 Aug 2009 02:49:19 -0700 (PDT)
Message-ID: <ec651854-f937-4089-aeb0-a1c8d609b5a1_at_o15g2000yqm.googlegroups.com>

On Aug 5, 4:04 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 3, 1:19 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
>
>
> > On Aug 2, 10:34 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > Something for you to experiment with:
> > > SELECT
> > >   TO_CHAR(SYSTIMESTAMP,'TZR')
> > > FROM
> > >   DUAL;
>
> > > -------
> > > -04:00
>
> > > SELECT
> > >   DBTIMEZONE
> > > FROM
> > >   DUAL;
>
> > > -------
> > > -05:00
>
> > > If the server's date is set to Jan 01, TO_CHAR(SYSTIMESTAMP,'TZR')
> > > returns -05:00 for my time zone.  You might be able to check if both
> > > values are the same, or different.  I do not know if this is the
> > > official solution, but it might give you a couple search keywords
> > > which might bring you close to the answer.
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
>
> > I have not had to use time zone or NLS parameters much so I checked
> > out Charles's query and based on my results I do not think this will
> > work on all systems:
>
> > Query
> >  select
> >    dbtimezone
> >   ,to_char(systimestamp,'TZR')
> >   ,current_timestamp
> >  from sys.dual
>
> > Ran on 10.2.0.3
> > DBTIME TO_CHAR(SYSTIMESTAMP,'TZR')
> > ------ --------------------------------
> > CURRENT_TIMESTAMP
> > ---------------------------------------------------------------------------
> > -04:00 -04:00
> > 03-AUG-09 01.08.59.158870 PM -04:00
>
> > Ran on 9.2.0.6 (on different server from above)
> > DBTIME TO_CHAR(SYSTIMESTAMP,'TZR')
> > ------ --------------------------------
> > CURRENT_TIMESTAMP
> > ---------------------------------------------------------------------------
> > +00:00 -04:00
> > 03-AUG-09 01.09.37.675886 PM -04:00
>
> > Eastern Standard Time is 5 hours from UTC (formerly GMT) for us and
> > Daylight savings time is 4 so both systems are using DST but the
> > dbtimezone appears to be set differently.  Looking at v$nls_parameters
> > I do not notice any differences.
>
> > The offset for a database location should be fixed so if you know the
> > correct offset you should be able to determine if DST is in use by
> > comparing the current offset to what is expected.  Not the best
> > answer, but the best I can do.  I haven't had time to check out the
> > other suggestion from Vlad yet.
>
> > HTH -- Mark D Powell
>
> Mark, thanks for performing the test.  I tested on 10.2.0.4 and
> 11.1.0.7 and received results as I originally posted.  Trying another
> server I received the same results as you.  It appears that DBTIMEZONE
> might be the time zone that was in effect when the database was
> created.  Some research on Metalink found this document: 340512.1
> which describes DBTIMEZONE and SYSTIMESTAMP.
>
> There is also an interesting post by Justin Cave here which shows how
> to see the time in another timezone:http://forums.oracle.com/forums/thread.jspa?messageID=3403047&
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Indeed, if you do not explicitly specify TIME_ZONE in CREATE DATABASE statement, DBTIMEZONE defaults to OS time zone at the time of database creation. And indeed, AT clause is pretty powerful, one of the reasons I'm tending to phase out DATE from my databases in favor of TIMESTAMP WITH TIME ZONE (if not for quite a few defects in initial implementation, I'd be using them everywhere since 9.2. 10.2 and 11.1 seem to have most of them fixed.) Another important thing is making sure db host syncs its time with at least stratum 2 NTP server regularly, otherwise your TIMESTAMPs are unreliable.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Aug 05 2009 - 04:49:19 CDT