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

[rathiyak]

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.

[sureshkumar]

Hello @rathiyak 

Kindly refer the below article

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

Regards,

Suresh

[rathiyak]

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 

[DesmondWOO]

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

[rathiyak]

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

[DesmondWOO]

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