topic Re: How to capture milli seconds When we use function datetime to convert to local time in Qlik Replicate

How to capture milli seconds When we use function datetime to convert to local time

Denial — Mon, 17 Apr 2023 09:46:35 GMT

Hi ,

Facing issue When we use function datetime to convert to local time, the milli second information is not captured. All the values are populated with 000000.

Also we use Audit column value as datetime($AR_H_COMMIT_TIMESTAMP,'utc','+10 hours'), since local time need to be ingested.

Tried to use the below expression but i am not able to test expression

datetime($AR_H_COMMIT_TIMESTAMP,'utc','+10 hours','yyyy-MM-dd hh:mm:ss.SSS')

Please Let me know how i can capture the details.

Thanks,

Re: How to capture milli seconds When we use function datetime to convert to local time

john_wang — Mon, 17 Apr 2023 10:01:05 GMT

Hello @Denial ,

Thanks for reaching out.

The header part information is retrieved from source database transaction log, or Replicate calculates based on some other basic information, if the transaction log does not record such detailed information eg commit time in millisecond then Replicate cannot get it.

Could you please let us know the source database type ? We'd like to confirm for you.

thanks,

John.

Re: How to capture milli seconds When we use function datetime to convert to local time

Denial — Mon, 17 Apr 2023 10:57:59 GMT

Hi John

The source we were trying for DB2 iseries

Thanks,

Re: How to capture milli seconds When we use function datetime to convert to local time

SachinB — Mon, 17 Apr 2023 12:07:33 GMT

Hello @Denial ,

You can use the strftime function to format a date and time value as a string with a specific format. To include milliseconds in the output, you can use the %f format code, which represents microseconds.

This will return the current date and time with microseconds, You can adjust the format string to match the format you need for your application.

strftime('%Y-%m-%d %H:%M:%f', 'now')

Let us know hope this works for you.

Regards,

Sachin B

Re: How to capture milli seconds When we use function datetime to convert to local time

Denial — Tue, 18 Apr 2023 04:23:01 GMT

Hi Sachin,

Thank you for your response , Yes it is working fine just a quick doubt as in the expression can we use UTC time to capture. i tried but it is not working Can you confirm on this .

Thanks,

Re: How to capture milli seconds When we use function datetime to convert to local time

SachinB — Tue, 18 Apr 2023 04:56:54 GMT

Hello @Denial ,

Thanks for the update.

The one which is representing I believe that is UTC time only, As replicate consider default time as UTC.

Regards,

Sachin B