topic Behavior of a timestamp field. in Integration, Extension & APIs

Behavior of a timestamp field.

marciofreireteixeira — Mon, 17 Aug 2020 12:05:49 GMT

Hi.

I'm doing a select an instance from a SqlServer database that has a timestamp field with the value '2020/08/13 14: 10: 49.093333'. But, It happens that when I write this instace in a QVD file, the value of this timesstamp field is zeroed at the end Ex: '2020/08/13 14: 10: 49.093000'. His behavior is like he is rounding the milliseconds.

Would you have any tips on how to solve this problem, writing the value read correctly in the QVD?

Re: Behavior of a timestamp field.

Kushal_Chawda — Mon, 17 Aug 2020 12:14:46 GMT

It's probably because of your timestamp variable in Main tab of the script

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] ';

change the highlighted part as below up to the decimal point you want

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fffffff]';

Re: Behavior of a timestamp field.

marciofreireteixeira — Mon, 17 Aug 2020 12:27:11 GMT

I did it, but the behavior remains the same.

Re: Behavior of a timestamp field.

marcus_sommer — Tue, 18 Aug 2020 12:07:45 GMT

You are right, Qlik is rounding the number because they has more digits as the from Qlik used numerical system could handle, see for it: Rounding-Errors

To store the whole value you could load these values as strings. Usually much better than this is to split the value into a date- and a time-field and only if the milliseconds are really important to store them within an extra millisecond-field. Most calculations/matchings could be done in this way and you will save a lot of RAM with it.

- Marcus

Re: Behavior of a timestamp field.

Kushal_Chawda — Tue, 18 Aug 2020 22:54:44 GMT

I tried loading the data value like below

Data: LOAD * Inline [ Time 2020/08/13 14:10:49.093333 ];

then I set the Timestamp format in load script exactly as data value like above

SET TimestampFormat='YYYY/MM/DD h:mm:ss[.fff]';

I can see the same value.

snapshot before Timestamp format set (you can see it is text. Left aligned)

snapshot after Timestamp format set (you can see it is Timestamp format. Right aligned)