Wrong time zone at local time zone?

cmuxrcmuxr Posts: 2
edited February 14, 2017 8:35AM in Data Compare for Oracle
I have the following table with data:
CREATE TABLE TEST 
(
  id number primary key, 
  T TIMESTAMP (6), 
  TZ TIMESTAMP (6) WITH TIME ZONE, 
  TLZ TIMESTAMP (6) WITH LOCAL TIME ZONE
); 

ALTER SESSION SET TIME_ZONE = 'UTC';  
INSERT INTO test(id, t, tz, tlz) values (1, 
TO_TIMESTAMP_TZ('2017-02-08 16:00 +01:00','YYYY-MM-DD HH24:MI TZH:TZM'),
TO_TIMESTAMP_TZ('2017-02-08 16:00 +01:00','YYYY-MM-DD HH24:MI TZH:TZM'),
TO_TIMESTAMP_TZ('2017-02-08 16:00 +01:00','YYYY-MM-DD HH24:MI TZH:TZM')
);

But Data Compare is showing me the tlz column as: 2017-2-8 8:0:0.000000000 +01:00
This is definitely wrong. So the Deployment Script is also wrong:
DECLARE
  null_value CHAR(1) := NULL;
  statement1 CHAR(63);
BEGIN
  statement1 := 'UPDATE fidopoli."TEST" SET "T"=:0, tz=:1, tlz=:2 WHERE "ID"=:w0';
  EXECUTE IMMEDIATE statement1 USING TIMESTAMP '2017-2-8 16:0:0.000000000', FROM_TZ(TIMESTAMP '2017-2-8 16:0:0.000000000', '+01:00'), TIMESTAMP '2017-2-8 8:0:0.000000000', 1;
END;
/
COMMIT;

It seems to me that the timestamp is fetched at time zone -07:00 (dbtimezone), but used at +01:00. Is there any way to set the session time zone at diff-time?

Comments

  • Eddie DEddie D Posts: 1,805 Rose Gold 5
    Hi, thank you for your forum post.

    Little weird, I believe I have a reproduction.

    I followed the DDL for the test table, in two different schemas and inserted the data as per your syntax into one of the schema's and performed a data compare.

    Performing a select statement using Oracle SQL Developer returns the following results:
            ID T                               TZ                                     TLZ                               
    ---------- ------------------------------- -------------------------------------- -----------------------------------
             1 08-FEB-17 04.00.00.000000000 PM 08-FEB-17 04.00.00.000000000 PM +01:00 08-FEB-17 03.00.00.000000000 PM  
    

    The comparison results and deployment script is as follows and I am at a loss to explain why the TLZ value becomes 10:0:0, where I expected it to be 15:0:0:
    
    DECLARE
      null_value CHAR(1) := NULL;
      statement1 CHAR(59);
    BEGIN
      statement1 := 'INSERT INTO eddie_dev2.timezonetest VALUES (:0, :1, :2, :3)';
      EXECUTE IMMEDIATE statement1 USING 1, TIMESTAMP '2017-2-8 16:0:0.000000000', FROM_TZ(TIMESTAMP '2017-2-8 16:0:0.000000000', '+01:00'), TIMESTAMP '2017-2-8 10:0:0.000000000';
    END;
    /
    COMMIT;
    

    A support ticket has been created for you here:
    https://redgatesupport.zendesk.com/agent/tickets/83115

    I will continue to investigate via the support ticket.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.