Timestamp presision

[yura_ratu]

Hi Community,

We have the following date format in database

05-JAN-16 19.07.23.933825

When I try to organize incremental load and find max value I've faced an issue when Qlikview cut milliseconds after 3rd figure

See the following expression

=Timestamp(Timestamp#('05-JAN-16 19.07.23.933825','DD-MMM-YY hh.mm.ss.ffffff'),'DD-MMM-YY hh.mm.ss.ffffff')

the result will be

05-Jan-16 19.07.23.933000

So that I can't accurately compare such dates, because 3 last figures have been lost

Any ideas how to solve it?

[maxgro · Accepted solution]

I had the same problem some months ago with an Oracle timestamp. More decimal in Oracle than in Qlik. I wasn't able to solve at the Qlik side, so the workaround I found was:

In Qlik I stored the last Oracle timestamp as text, no figures was lost. The timestamp was converted to char at the Oracle side (TO_CHAR).

In the incremental I converted (at the Oracle side, TO_TIMESTAMP) the timestamp stored in Qlik as text  

back to an Oracle timestamp.

[sunny_talwar]

There are some links where people have tried to do it (and it seemed it worked for them), but I was unable to replicate the same expression for yours. May be you can figure something out:

How to format Qlikview Timestamp upto microseconds?‌

http://stackoverflow.com/questions/28853999/qlikview-timestamp-formatting-upto-microseconds

[marcus_sommer]

What if if you used a pure number like: num(timestamp#(YourField, YourFormat))? Whereby normally should qv be able to handle a number like this although it has restrictions by large numbers respectively a lot of digits.

Another possibility might be to use two fields - one for dates and one for times for your incremental logic and for your datamodel itself, then: The Importance Of Being Distinct

- Marcus

[yura_ratu]

Hi Marcus,

I tried to do so, but it didn't work. If put this expression to the text box

=num(Timestamp#('05-JAN-16 19.07.23.933825','DD-MMM-YY hh.mm.ss.ffffffffff'),)
&chr(10)
&num(Timestamp#('05-JAN-16 19.07.23.933826','DD-MMM-YY hh.mm.ss.ffffffffff'),)

There will two same numbers, however there is a difference in microseconds

[rbecher]

Hi Yurii,

do you need this time precision at all? If not, you can round or cut it of before comparing.

- Ralf

[marcus_sommer]

Try it with two or maybe three fields, like:

subfield(YourField, ' ', 1) as DateString

mid(subfield(YourField, ' ', 2), 1, 10) as TimeString

mid(subfield(YourField, ' ', 2), 11) as MillisecondString

and then converting it in numbers and if you need it you could simply add those fields.

- Marcus

[yura_ratu]

Thanks, it helps in this particular scenario. However it's sad there is no way to deal with microseconds within Qlikview, without workarounds like you and Marcus proposed bellow.

[simonaubert]

Hello,

You can vote on this idea : https://community.qlik.com/t5/Ideas/Qlik-Timestamp-precision-go-to-the-nanesecond-equivalent-to-DB/i...

Best regards,

Simon