topic Re: Timezone 'problems' in Operating System - HP-UX

Timezone 'problems'

Kyris — Thu, 19 Apr 2007 01:42:54 GMT

We have Oracle 9i. Some tables hold their information in GMT. After the change in March to BST, we need to change the date column in these tables to -1 hour, so that it reflects GMT.
A table (gmt_offset) was set up that has the value of ZERO during the winter months and 1 from March to October, which needs to be manually updated when the clocks go forward. Then this procedure converts things to GMT :
CREATE OR REPLACE PROCEDURE "UPDATE_TO_GMT" AS

BEGIN
--HARDCODED|0 SHOULD BE VALUE OF GMT_OFFSET TABLE
UPDATE SCAN_BUFFER_TABLE SET TIMEDATE = (TIMEDATE - (0 *(1/24)));
UPDATE TIP_BUFFER_TABLE SET TIMEDATE = (TIMEDATE - (0 *(1/24)));
UPDATE BSM_BUFFER_TABLE SET TIMEDATE = (TIMEDATE - (0 *(1/24)));
COMMIT;
END;
/
Unfortunately this manual change of the gmt_offset table is not satisfactory(so the new boss says!). We changed the procedure to the one attached but the result we get for the NZONES variable is '+00:00' and not '+01:00'. Can someone please suggest a better way of doing this? We do not have that knowledge to do it in any other way. Perhaps the person that designed the DB has done it this way because there was no other solution. Was he right or just wrong/lazy??

Re: Timezone 'problems'

Andrew C Fieldsend — Fri, 20 Apr 2007 03:12:38 GMT

It looks as though this might be historical, as there was no support for time zones or DST in Oracle before 9i.

From 9i onwards, you can use the timestamp data type to store date-and-time information with associated time zone information. There's a brief introduction to the subject at http://www.devx.com/dbzone/Article/30501.

There is, of course, a caveat: if you use this functionality, you become dependent on Oracle patches for changes to the DST rules (such as the recent US DST change). Also, this will mean a change to the application tables, which may involve reworking some of your application...

Re: Timezone 'problems'

Kyris — Fri, 20 Apr 2007 05:17:56 GMT

Andy thanks for your response. The link sent does not seem to work.
My problem is a procedure that we need to change so that it does the job that it should do, as per my post.
The select statement -

SELECT tz_offset(tzname) from V$TIMEZONE_NAMES;

gives me '+01:00', which is what I need as we are now in GMT+1. When the clocks change at the end of October, will this value change to '+00:00' ?? Does anyone know this answer, as this will be a way out, but only if it changes.
thanks

Re: Timezone 'problems'

Yogeeraj_1 — Fri, 20 Apr 2007 06:06:55 GMT

hi,

what does this query return?
select dbtimezone from dual;

revert.

kind regards
yogeeraj

Re: Timezone 'problems'

Kyris — Fri, 20 Apr 2007 08:07:49 GMT

Thanks for the reply...

The querry returns '+01:00', which is what I need but will it return '+00:00' after the clocks change in October- which is GMT+0??

thanks
kyris

Re: Timezone 'problems'

Andrew C Fieldsend — Fri, 20 Apr 2007 09:41:26 GMT

From the Oracle 9iR2 SQL reference manual: "Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly."

I assume this means that the result of your select will change as DST does.