Re: 10gR2 - quick Time Zone question
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 3 Aug 2009 10:19:55 -0700 (PDT)
Message-ID: <1904f7b5-f403-4126-baa2-c12ac2861ac6_at_v20g2000yqm.googlegroups.com>
On Aug 2, 10:34 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 2, 10:34 am, Jeremy <jeremy0..._at_gmail.com> wrote:
>
> > Hi
>
> > How can I tell from within PL/SQL whether SYSDATE is in the daylight
> > saving time (DST) date range?
>
> > It is not a fixed date each year, is there a function that will tell me
> > this?
>
> > --
> > jeremy
>
> 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.
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.
Date: Mon, 3 Aug 2009 10:19:55 -0700 (PDT)
Message-ID: <1904f7b5-f403-4126-baa2-c12ac2861ac6_at_v20g2000yqm.googlegroups.com>
On Aug 2, 10:34 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 2, 10:34 am, Jeremy <jeremy0..._at_gmail.com> wrote:
>
> > Hi
>
> > How can I tell from within PL/SQL whether SYSDATE is in the daylight
> > saving time (DST) date range?
>
> > It is not a fixed date each year, is there a function that will tell me
> > this?
>
> > --
> > jeremy
>
> 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 -- Received on Mon Aug 03 2009 - 12:19:55 CDT