1748252 Members
4036 Online
108760 Solutions
New Discussion юеВ

Timezone 'problems'

 
SOLVED
Go to solution
Kyris
Frequent Advisor

Timezone 'problems'

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??
5 REPLIES 5
Andrew C Fieldsend
Respected Contributor

Re: Timezone 'problems'

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...
Kyris
Frequent Advisor

Re: Timezone 'problems'

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
Yogeeraj_1
Honored Contributor

Re: Timezone 'problems'

hi,

what does this query return?
select dbtimezone from dual;

revert.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Kyris
Frequent Advisor

Re: Timezone 'problems'

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
Andrew C Fieldsend
Respected Contributor
Solution

Re: Timezone 'problems'

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.