Wrong time zone at local time zone?

cmuxr

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?

Eddie D

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