Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Shaikhsaqib_
Contributor
Contributor

Convert timezone.

Hi,

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

Labels (1)
1 Solution

Accepted Solutions
john_wang
Support
Support

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):

john_wang_0-1640312770932.png

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

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

6 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Shaikhsaqib_
Contributor
Contributor
Author

Hello @john_wang,

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

Shaikhsaqib__0-1640310207424.png

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

 

Thanks alot.

 

john_wang
Support
Support

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):

john_wang_0-1640312770932.png

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

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
anat
Master
Master

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

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
kmmreddy001
Contributor
Contributor

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