In general, for every target table in the replication task, a change table with the corresponding name is maintained by Qlik Replicate in the target endpoint. A change table contains the original table columns, and header columns (for additional details please refer to the Replicate Users guide).
When applying changes to the target tables, you can determine (via task setting) whether to replicate the changes to the target only, store changes in the Change Tables, or both.
The change timestamp of the replicated data is kept in the Replicate Change Table as part of the header columns. The column name is header__timestamp and it includes the UTC timestamp of the original change (the value may be approximate).
If you choose to keep Change Tables then, to get the change timestamp you can access the header__timestamp on the change table.
Alternatively, you can get this info by adding to the target table a new column and with a transformation rule populate it with the value of the required header timestamp. To define the column with the transformation rule go to:
- Table settings -> transform --> add column
- On the added column, choose expression builder (fx) --> headers tab and choose the required header column
For instance:
AR_H_TIMESTAMP - returns the source host time zone of the change in source.
AR_H_COMMIT_TIMESTAMP - returns the source host time zone of the COMMIT in the source.
If you wish, you can populate the new column with the timestamp of the time that the change was applied in the target. To do this you should:
- In table setting on the new added column, enter the expression builder (fx) --> Functions tab
- From the function list on the left choose 'Date and Time'
- On the right side, you’ll get the optional date and time functions that can be used in the transformation expression
For example:
datetime('now') returns Replicate host UTC time when the change was applied to the target.
datetime('now','localtime') returns Replicate host local time when change was applied to target.
Example adding 2 hours:
datetime('now','+2 hours','localtime')
Note: You can use sqlite functions to set this at your convenience, but you can not change the original value.
Please refer to the following link for additional info bout the available SQLite date and time functions:
https://www.sqlite.org/lang_datefunc.html
