Replicate automatically adding one hour to the timestamp

[MoeE]

Hi,

A client pointed out an issue where Qlik Replicate adds one hour to the timestamp when the source column is of the type TIMESTAMP WITH LOCAL TIMEZONE. From what we've seen, only records with timestamps from outside the Daylight savings period have one hour added to them, while records from within the Daylight savings time period are correctly replicated in the target.

Their source endpoint is Oracle and target was ADLS, but I was able to recreate this issue going from Oracle to SQL Server. I believe now that Qlik Replicate adds one hour. This is because the datatype mapping from Oracle is:

Therefore, Replicate must be adding one hour so that the time appears in Daylight savings time. 

Is there any way to make it so that the timestamp in the target and source are exactly the same? I think the issue here lies with the fact that their source data doesn't actually have the timezone attached to the record.

This is my create table statement:

CREATE TABLE "C##NORTHWIND"."BIRDS_WITH_TIMESTAMP" 
(
    "BIRD_ID" VARCHAR2(2 BYTE) NOT NULL, 
    "BIRD_NAME" VARCHAR2(50 BYTE) NOT NULL, 
    "HABITAT" VARCHAR2(50 BYTE), 
    "LIFESPAN" NUMBER(38,10), 
    "LENGTH" NUMBER(38,10), 
    "WEIGHT" NUMBER(38,10), 
    "AGE" NUMBER(38,10), 
    "REC_COMMIT_TIME" VARCHAR2(50 BYTE), 
    "REC_INS_TIME" VARCHAR2(50 BYTE), 
    "IssueTimestampColumn" TIMESTAMP WITH LOCAL TIME ZONE,  -- Store as TIMESTAMP with local timezone
    CONSTRAINT "BIRDS_WITH_TIMESTAMP_PK" PRIMARY KEY ("BIRD_ID", "BIRD_NAME")
);

I added supplemental logging to the table then added these records: 

INSERT INTO "C##NORTHWIND"."BIRDS_WITH_TIMESTAMP" 
("BIRD_ID", "BIRD_NAME", "HABITAT", "LIFESPAN", "LENGTH", "WEIGHT", "AGE", "REC_COMMIT_TIME", "REC_INS_TIME", "IssueTimestampColumn") 
VALUES 
('1', 'Sparrow', 'Urban', 3, 15.0, 0.025, 1, '2024-10-22 10:00:00', '2024-10-22 10:00:00', TO_TIMESTAMP('15/MAY/24 08:00:00.000000000 AM', 'DD/MON/YY HH12:MI:SS.FF AM'));

INSERT INTO "C##NORTHWIND"."BIRDS_WITH_TIMESTAMP" 
("BIRD_ID", "BIRD_NAME", "HABITAT", "LIFESPAN", "LENGTH", "WEIGHT", "AGE", "REC_COMMIT_TIME", "REC_INS_TIME", "IssueTimestampColumn") 
VALUES 
('2', 'Robin', 'Forests', 2, 20.0, 0.075, 1, '2024-10-22 10:00:00', '2024-10-22 10:00:00', TO_TIMESTAMP('15/DEC/24 08:00:00.000000000 AM', 'DD/MON/YY HH12:MI:SS.FF AM'));

The column you should focus on is called "IssueTimestampColumn". One record is inside the daylight savings period, while the other is not. This is what the source data looks like: 

and this is what it looks like on the target (SQL Server):

Notice that the time for the row outside the daylight savings time has one hour added to it.

Any ideas as to how this could be fixed? Help with this is appreciated, thank you. 

Regards,

Mohammed

[Dana_Baldwin]

Hi @MoeE Please stop and resume the task. Refer to: Impact of DST change on Qlik Replicate | Qlik Replicate Help

[MoeE · Accepted solution]

Hi Dana,

Thanks, we've already tried this but it looks like it's the nature of the actual datatype that displays a different time depending on the timezone of the Oracle session used to query the table.

Regards,

Mohammed