Oracle 9i and Daylight Saving

[Albert Goodman]

Hi,

Does anyone know of a good approach to take in relation to the automatic switch back of system clock after daylight saving ends on a system running Oracle 9i?

As I understand it, when daylight saving ends, the system clock switches back from 2am to 1am and that this can cause the database some grief. To be safe, it was suggested that the database should be stopped prior to say 1am for just over an hour and restarted after the clock has switched back. Does anyone know of a better way?

We are using NTP to synchronise our system clock with an external time source.

Thanks in advance and points will be allocated.

[Indira Aramandla]

Hi Albert,

Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly. The datetime value is sufficient for Oracle to determine whether daylight savings time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight savings goes into or comes out of effect.

For example, in the US-Pacific region, when daylight savings goes into effect, the time changes from 2 AM. to 3 AM. The one hour interval between 2 and 3 AM. does not exist. When daylight savings goes out of effect, the time changes from 2 AM. back to 1 AM, and the one-hour interval between 1 and 2 AM is repeated.

To resolve these boundary cases, Oracle uses the TZR and TZD format elements, as described in Table 2-15. TZR represents the time zone region in datetime input strings. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbreviated form of the time zone region with daylight savings information. Examples are 'PST' for US/Pacific standard time and 'PDT' for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view.

Refer to Note:149120.1 "Oracle9i Daylight Savings feature in Datetime datatypes" in Metalink. The purpose of this note is to introduce this Daylight Savings feature.

Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly.


Indira A

[Yogeeraj_1]

hi Albert,

also allow me to add the Indira's explanation above that there are no issues in the database, only in applications that use the database.

All things inside the database are governed by the SCN, a number, a ticker -- it is not based on the wall clock.

Applications can be affected as all of a sudden there are two 1AM's on a given day.

hope this helps too!
kind regards
yogeeraj

[Albert Goodman]

We've also got SAP R/3 running. I suppose that complicates things somewhat?

[Tvs]

Hi

The hp server take the automatic daylight saving from the following file. and hp server can automatically adjust as per the day light settings.

The time zone setting is in /etc/TIMEZONE

#cat /etc/TIMEZONE

TZ=GMT0BST
export TZ

this entry is taking the parameter from /usr/lib/tztab


# grep GMT0BST /usr/lib/tztab

# Greenwich Mean Time, British Summer Time
GMT0BST
0 3 25-31 3 1983-1984 0 BST-1
0 3 23-29 3 1985-1990 0 BST-1
0 3 25-31 3 1991-1995 0 BST-1
0 2 25-31 3 1996-2038 0 BST-1
0 1 25-31 10 1983-1985 0 GMT0
0 1 23-29 10 1986-1994 0 GMT0
0 1 18-24 10 1995 0 GMT0
0 1 25-31 10 1996-2038 0 GMT0

for more information regarding the settings and parameter u can find in.

# man tztab


if any application is ruuning pls stop all the application ( like sap , oracle) at the time of day light changings.
for example in UK the winter day light change is happening on October 31 at 1.00 clock . it can effect the constitencey of the application.

regards

tvs

[Yogeeraj_1]

hi

In Oracle 9i and up, timezone support is built-in to the TIMESTAMP WITH TIMEZONE datatype.

I don't know if the SAP R/3 know how to benefit from that. Maybe you should contact support.

kind regards
yogeeraj

[Brian Crabtree]

Albert,

For where we are, we bring SAP down for the fall back portion, per SAP.

Not sure if that helps.

Brian

[Albert Goodman]

Thank you all for your kind responses. I was hoping for a more innovative approach but Brian thanks for alluding to SAP Note 102088

I will close this thread and open another one to see if anyone know how to turn off daylight saving on a HP-UX 11i system in the least intrusive way. I believe I can fudge the tztab table but am not sure if it is wise