topic Re: Convert timezone. in Qlik Replicate

Convert timezone.

Shaikhsaqib_ — Fri, 24 Dec 2021 01:19:52 GMT

Hi,

I m passing UTC timezone datetime from my source database and want EST timezone datetime in my target database column. Please assist!

Re: Convert timezone.

john_wang — Fri, 24 Dec 2021 01:29:48 GMT

Hello @Shaikhsaqib_ ,

You can use transformation. There are rich functions, see Date and Time .

If you need a sample, let me know the source/target DBs types, and sample data, I'd love to show you the usage.

Hope this helps.

Regards,

John.

Re: Convert timezone.

Shaikhsaqib_ — Fri, 24 Dec 2021 01:45:19 GMT

Hello @john_wang,

I m using MySQL as a source and snowflake as a target db.

The above-mentioned are in UTC and I want to convert this column date in EST at target DB (snowflake).

Thanks alot.

Re: Convert timezone.

john_wang — Fri, 24 Dec 2021 02:27:47 GMT

Hello @Shaikhsaqib_ ,

Well, you are asking a rather good question 😊

I'm guessing you are talking about the data type is "TIMESTAMP". MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval, it's MySQL design. Now the retrieval is Qlik Replicate, so far you can set your database timezone in MySQL Endpoint Advance page (In my sample I selected China/HK timezone):

If you are talking about other data type then transformation helps always.

Regards,

John.

Re: Convert timezone.

anat — Fri, 24 Dec 2021 03:43:02 GMT

ConvertToLocalTime(DATE#(SUBFIELD('2020-05-17 17:45:35.6524922 +00:00', '.', 1), 'YYYY-MM-DD hh:mm:ss'), 'Eastern Time (US & Canada)')

Re: Convert timezone.

john_wang — Mon, 27 Dec 2021 23:45:30 GMT

Hello @Shaikhsaqib_ ,

It has been several days since we heard from you. I would like to ensure that we helped you with your issue. Feel free to let me know if you need any additional assistance.

BTW, seems @anat is explaining the issue in another product instead of Qlik Replicate.

Thank you,

John.

Re: Convert timezone.

kmmreddy001 — Tue, 11 Apr 2023 20:37:52 GMT

Hi, Is there any simple function to convert the existing date time field in the source column to EST at target?

both the Source and the target are PostgreSQL