Qlik Replicate: Microseconds are truncated when using the Date/Time functions in the transformation

[DesmondWOO]

In Qlik Replicate when manipulating datetime data on the transformation page, the microsecond precision is truncated to the first three digits after the transformation is performed.

Qlik Replicate utilizes SQLite syntax, which is a limitation since SQLite only supports fractional seconds up to SS.SSS. If we perform the same syntax in an SQLite command prompt, we would see the same result.

sqlite> select strftime('%Y-%m-%d %H:%M:%f','2024-06-27 12:00:00.123456','+8 hours');
2024-06-27 20:00:00.123

Resolution

After manipulating the datetime, you can treat it as a string and concatenate the microseconds using a combination of SQLite functions, like this:

substr(strftime('%Y-%m-%d %H:%M:%f', $TS, '+8 hours'), 1, 19) || substr(strftime('%Y-%m-%d %H:%M:%f', $TS), 20)

Environment

• Qlik Replicate All Versions