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

[Denial]

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,

[john_wang]

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.

[Denial]

Hi John 

The source we were trying for DB2 iseries

Thanks,

[SachinB · Accepted solution]

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

[Denial]

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,

[SachinB]

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