Re: 10gR2 - quick Time Zone question
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 4 Aug 2009 17:04:56 -0700 (PDT)
Message-ID: <b44ef07a-ff88-4a1d-a71f-54d96037d0c8_at_j32g2000yqh.googlegroups.com>
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
Date: Tue, 4 Aug 2009 17:04:56 -0700 (PDT)
Message-ID: <b44ef07a-ff88-4a1d-a71f-54d96037d0c8_at_j32g2000yqh.googlegroups.com>
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.
Received on Tue Aug 04 2009 - 19:04:56 CDT