Re: 10gR2 - quick Time Zone question
Date: Mon, 3 Aug 2009 06:58:15 -0700 (PDT)
Message-ID: <bf022598-cfe8-42c6-be8f-771998d6e1d7_at_b14g2000yqd.googlegroups.com>
On Aug 3, 6:34 am, 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'd recommend to set the database time zone to abbreviated region name rather than offset from UTC (which may, and usually does, change with daylight savings.) For example,
ALTER DATABASE SET TIME_ZONE='America/New_York';
followed by instance bounce. In this case, Oracle will automagically correct offset from UTC using built-in time zone dictionary for any particular day and the database will 'know' if DST is in effect. The dictionary is updated from time to time with one-off patches to reflect changes in DST rules and addition/removal of time zone abbreviations.
However, Oracle recommends to set database time zone to UTC to avoid time zone conversions, especially between databases, and possibly improve performance. DST does not apply to UTC, so it's a safe and sane setting, too.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Mon Aug 03 2009 - 08:58:15 CDT