topic Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table in Qlik Replicate

How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

rathiyak — Thu, 18 Jul 2024 10:59:10 GMT

I have an oracle source table with a column datetime datatype.

its store time in cet/cest timezone.

on target side i have a postgresql table with a column having timestamp with timezone datatype.

i want to convert cet/cest time to utc while saving / add a proper time zone to date time coming from oracle.

Also this transformation should take care of CET to CEST change / CEST to CET change happening twice a year.

Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

sureshkumar — Thu, 18 Jul 2024 16:03:13 GMT

Hello @rathiyak

Kindly refer the below article

Timezone Conversion - Oracle CET to Kafka (Avro) U... - Qlik Community - 1877441

Regards,

Suresh

Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

rathiyak — Fri, 19 Jul 2024 06:50:04 GMT

i tried datetime ($CREATED || '+01:00'), it works but will have to modify it when day light saving changes.

I need solution to handle it automatically

Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

DesmondWOO — Fri, 19 Jul 2024 11:12:01 GMT

Hi @rathiyak ,

Would it be acceptable if you check the date and time to address the daylight saving issue? For example,

CASE
WHEN $DT >= '2024-03-31 02:00' AND $DT < '2024-10-27 03:00' THEN "CEST"
WHEN $DT >= '2024-10-27 03:00' AND $DT < '2025-03-30 02:00' THEN "CST"
END

Regards,
Desmond

Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

rathiyak — Mon, 22 Jul 2024 02:05:56 GMT

It would have similar problem, will have to modify it again for next years dates

Re: How to convert CET/CEST datetime from an oracle column to utc timesatmp for postgresql target table

DesmondWOO — Tue, 23 Jul 2024 07:20:09 GMT

Hi @rathiyak ,

I don’t have any better ideas for this issue, but I suggest adding daylight saving dates and times for the next few years. This way, you won’t need to update the expression annually.

Regards,
Desmond