AR_H_TIMESTAMP in global rules is returned in UTC, need to convert to Eastern Time.

[SBReddy]

AR_H_TIMESTAMP is returned in UTC and I need to convert it to Eastern time.

strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-4 hour') is working but I need it to take DST into consideration as well. 

datetime($AR_H_TIMESTAMP,'localtime') is returning the time in UTC  since the replicate server is in UTC timezone and it is also removing the milliseconds. Can you please help.

Expected Format of Date time in EST:

10-JUN-24 07.05.06.508609000 AM

[john_wang]

Hello @SBReddy ,

Welcome to Qlik Community forum and thanks for reaching out here!

There are some options with limitations:

1- You may try expression

     strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, 'localtime')

     however only 3 digital milliseconds are kept in the result.

2- Quote: ... but I need it to take DST into consideration as well ...

     Not sure you mean the Replicate Server DST or the value of the source table records will be converted. If it's Replicate server, I'd like suggest to modify the expression manually. if the DST depends on the records values then you need use case...when... statement to call different strftime expressions.

3- Quote: Expected Format of Date time in EST: 10-JUN-24 07.05.06.508609000 AM

     Personally I do not think it's possible as SQLite support 3 digital milliseconds only. Unless you retrieve the 9 digital milliseconds with expression in transformations.

Hope this helps.

John.

[SBReddy]

Hello @john_wang, thank you for the reply.

1.  strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, 'localtime') is still giving me the time in UTC.

AR_H_TIMESTAMP|AR_H_TIMESTAMP_EST
2024-06-12 13:49:41.369396|2024-06-12 13:49:41.447000

Is the ConvertToLocalTime function available to use from Qlik replicate? I'm getting a no such function error when trying.

2. For DST, when the time shifts in winter or summer, I want to be able to use the AR_H_TIMESTAMP without any modifications. If I hardcode as strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-4 hour'), I would need to change it when the time shifts.
Yes, it is a replicate server, can you help me with options on how to handle the time shift.

3. Yes, as of now strftime('%Y-%m-%d %H:%M:%S', $AR_H_TIMESTAMP, '-4 hour')|| '.' || substr($AR_H_TIMESTAMP, 21, 9) is currently working for me to get more than 3 digits of milliseconds with transformation.

[john_wang]

Hello @SBReddy ,

Thanks for the detailed information.

1. Any valid SQLite functions can be used in Qlik Replicate transformation however I think we need to find out why the function does not work. What's the source DB type and version? In my labs the Oracle source works fine. And my Replicate running on Windows.

2. Yes it's doable by judging the current date by function date('now'), then use the case ... when ... function like (pseudo code):

     WHEN date('now')=winter THEN strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-4 hour')

     ELSE strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-5 hour') END

     Please take note the date('now') returns Qlik Replicate Server system date.

3. You are correct! this is exactly what I meant.

Best Regards,

John.

[SBReddy]

Hello @john_wang ,

1. Source is DB2 iseries and replicate is running on linux.

2. CASE
WHEN date('now')=winter THEN strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-4 hour')
ELSE strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-5 hour')
END

The case statement is giving an error  "no such column: winter"

Can you please help.

[john_wang]

Hello @SBReddy ,

1. It works correctly for me. Please take note that the original $AR_H_TIMESTAMP value is represented as the local time on Replicate Server.  When the source database and Replicate Server are both in the same timezone, the timestamp will reflect the approximate time of the actual Change Operation.

2. It was pseudo code. Blow is a sample which can be run in Replicate task

  CASE WHEN date('now') > '2024-06-12'
  THEN strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '+8 hour')
  ELSE strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '+7 hour')
  END

    It need to be tailored to fit your needs.

Hope this helps.

John.

[SBReddy]

Thank you @john_wang . It isn't working for me since this replicate server is in UTC. I tested in another server which had Eastern timezone and it is working fine.

[john_wang]

Hello @SBReddy ,

Glad to hear that! Thank you so much for your great support.

Regards,

John.