Re: daylight savings and universal coordinated time

From: Leo Salminen <leo.salminen_at_ntc.nokia.com>
Date: 1998/01/26
Message-ID: <34CC4B85.534B@ntc.nokia.com>

Gerardo Odorizzi wrote:

> 
> Does Oracle7/8 know about daylight savings time and/or
> universal coordinated time.
> 
> We have a customer where the database is running on a
> VMS server that is in standard time.  However the
> client PC's are in daylight savings time.  The customer
> wants the timestamps stored in standard time and
> excepts the client applications (I assume through
> SQL*Net) to be able to retrieve the standard time dates
> and times, and convert them to daylight savings time.
> 
> I see nothing in any documentation or the Oracle Web
> site that discusses this topic.
> 
> I don't believe that Oracle handles this, but, I need
> confirmation of this for my customer.

I asked Oracle support (Finland) about time zones and day light saving time
and the answer I got is following:

There is a development request made about this problem (Bug 156629) for our developers.
ENH: INIT.ORA PARAMETER TO SPECIFY THE TIME-ZONE A DATABASE IS UNDER In the distributed environment it is essential to be able to comprare time
for multiple databases. The application developer needs to be able to tell what timezone a database is in so he can compare apples with apples.
An init.ora parameter will allow them to query from v$parameter for the information.

The following example is for replication environments. Would it help you ?

REM

REM
REM
REM CONFLICT RESOLUTION IN SYMMETRIC REPLICATION: TIMESTAMP EXAMPLE REM
REM The following is an example of using the LATEST TIMESTAMP method in REM conflict resolution. This example does not account for differences in
REM timezones. You are encouraged to build your timestamp trigger using GMT
REM to accomodate timezone differences. REM
REM

REM
REM Oracle Disclaimer:
REM
REM This script is provided for educational purposes only. REM It is NOT supported by Oracle World Wide Technical Support. The script
REM has been tested and appears to work as intended. However, you should
REM always test any script before relying on it. REM
REM
REM Please proofread this script prior to using it. REM Due to differences in the way text editors, email packages and operating
REM systems handle text formatting (spaces, tabs and carriage returns), this
REM script may not be in an executable state when you first receive it. Check
REM over the script to ensure that errors of this REM type are corrected.
REM
REM
REM
REM

REM The primary author of these scripts is: REM
REM Richard Jobin, Senior Technical Analyst REM Oracle Worldwide Support
REM
REM

REM

REM Script 1. To be run at the MASTER DEFINITION SITE. REM

REM
REM (1) Suspend replication activity on the master definition site for SCOTT.
REM

connect repadmin/repadmin
execute dbms_repcat.suspend_master_activity('SCOTT');

REM

REM (2) Add a column to be used in conflict resolution to the table

REM     and replicate the column to the other tables using the 
REM     other tables using the DBMS_REPCAT.ALTER_MASTER_REPOBJECT 
REM     procedure.  The following adds a date column to SCOTT's DEPT

table.
REM

execute
dbms_repcat.alter_master_repobject('SCOTT','DEPT','TABLE','ALTER TABLE SCOTT.DEPT ADD (LAST_UPDATE DATE)', 'Changed ' || SYSDATE || ' by
' || USER, FALSE); REM

REM (3) Confirm the STATUS of the object of the replication schema

REM     by querying the DBA_REPOBJECT view. 
REM     Check for ERROR or INVALID status.

REM

select oname, status from dba_repobject where oname = 'DEPT';

REM

REM (4) If the status of the object is ERROR or INVALID do the following:
REM

REM      SQL> execute dbms_repcat.alter_master_
repobject(sname=>'scott',     
REM                 oname=>'emp',type =>'table',DDL_TEXT =>NULL); 
REM  
REM             If the pl/sql procedure successfuly completes, it will 
REM             VALIDate the object again.

REM
REM

REM
REM (5) Populate the new column with non-null values.

REM     Disable replication for your session 
REM     so that you will not fire the $RT trigger and put 
REM     entries into the deferred queue.

REM

desc scott.dept;

execute dbms_reputil.replication_off;
update scott.dept set last_update=trunc(SYSDATE); commit;

REM

REM (6) Now re-enable replication for your session. REM

execute dbms_reputil.replication_on;

REM

REM Script 2. To be run the MASTER SITE(s) REM

REM
REM (1) On the MASTER SITE disable replication and populate the DEPT REM table with non-null values.
REM

connect repadmin/repadmin
desc scott.dept;
execute dbms_reputil.replication_off;
update scott.dept set last_update=trunc(SYSDATE); commit;
execute dbms_reputil.replication_on;

REM

REM (2) Now it is time to register a conflict resolution routine.

REM     We will create a database trigger on the MASTER DEFINITION SITE 
REM     that will populate the LAST_UPDATE column of the DEPT table 
REM     column of the DEPT table with the current date and timestamp
using 
REM     sysdate.  We should only fire the trigger when the changes to
the  
REM     table are being made by the LOCAL transaction. 
REM

REM

REM Script 3. To be run at the MASTER DEFINITION SITE. REM

REM
REM (1) Create the database trigger on the SCOTT.DEPT table. REM Name the trigger SCOTT.DEPT_CONFLICT. REM

connect repadmin/repadmin

create trigger SCOTT.DEPT_CONFLICT
before
insert or update
on SCOTT.DEPT
for each row
begin

IF DBMS_REPUTIL.FROM_REMOTE = FALSE then :NEW.LAST_UPDATE := SYSDATE;
END IF;
end;
/
REM

REM (2) Register the trigger in SCOTT's replication schema. REM

execute
dbms_repcat.create_master_repobject('SCOTT','DEPT_CONFLICT','TRIGGER');

REM

REM (3) Now create a column group and define column(s) within the group. REM An asterisk (*) indicates all columns will be used as part REM all columns will be used as part of the column group. REM

execute
dbms_repcat.make_column_group('SCOTT','DEPT','DEPT_UPDATE_CG','*');

REM

REM (4) Register an UPDATE CONFLICT resolution routine REM using the LATEST TIMESTAMP method. REM

execute
dbms_repcat.add_update_resolution('SCOTT','DEPT','DEPT_UPDATE_CG',1,'LAT EST TIMESTAMP', 'LAST_UPDATE'); REM

REM (5) Regenerate replication support for the DEPT table.

REM     This regenerates the triggers and procedures 
REM     to include the conflict resolution.

REM

execute
dbms_repcat.generate_replication_support('SCOTT','DEPT','TABLE');

REM

REM (6) Now resume replication activity. REM

execute dbms_repcat.resume_master_activity('SCOTT');

REM

REM (7) Query the dba_repcat view to see a NORMAL status. REM

select * from dba_repcat;

REM

REM     If status is NORMAL you are now ready to perform some 
REM     DML on the DEPT table and test the LATEST TIMESTAMP 
REM     conflict resolution routine.  The most recent update to the row 
REM     will resolve the conflict.  Consider standardizing your
timestamping 
REM     prompt mechanism by converting the timestamp to Greenwich Mean
Time 
REM     (GMT).  This will allow you to accomodate multi time zone

masters..
REM

Received on Mon Jan 26 1998 - 00:00:00 CST