Hello all,
On Qlik, Timestamp precision goes to the milisecond. That may seems sufficient but it's not. On many database, Timestamp precision goes to the nanosecond.
On Oracle :
https://docs.oracle.com/database/nosql-12.1.4.3/SQLForNoSQL/timestamp.html
"Timestamp values have a precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds, and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum. "
On Hive :
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps...
Why does it matter? Do you really need such an amazing precision for analysis? Come on !
Incremental load, that's why !
You get a load with a record at 2020-11-03 01:02:03.123456789
Qlik Sense stores 2020-11-03 01:02:03.123
Next load=> You load every record where TS> 2020-11-03 01:02:03.123
Same record is here !!
Oh, you gonna say : just round the TS like where (TS with a milisecond)>2020-11-03 01:02:03.123.
Well that does not work either if you have a record at 2020-11-03 01:02:03.1239999.
There is, however a turnaround : going full text with something like that
LOAD LCC_MAJ_DT,
LCC_MAJ_DT_TIMESTAMP,
LCC_LIGNE_ID,
LCC_LIGNE_DETAIL_ID
;
SQL SELECT
LCC_MAJ_DT as LCC_MAJ_DT_TIMESTAMP,
to_char(LCC_MAJ_DT,'YYYY_MM_DD HH24:MI:SS.FF') as LCC_MAJ_DT,
to_char(LCC_LIGNE_ID) as LCC_LIGNE_ID,
to_char(LCC_LIGNE_DETAIL_ID) as LCC_LIGNE_DETAIL_ID
FROM LSNTER."DIS_F_CC_LIGNE" where LCC_MAJ_DT>TO_TIMESTAMP('$(t_date)','YYYY_MM_DD HH24:MI:SS.FF')
;
(edit : do not use YYYY-MM-DD HH24:MI:SS.FF=>> Qlik will understand it's a timestamp and stupidly recast it to TS)
But this is quite inefficient when you have to do that on hundred of millions of line ! And also you have to store it in a string format in qvd.. costly.
Bi Consultant (Dataviz & Dataprep) @ Business & Decision