I'm having the usually difficultly with timestamps in QlikSense, being generally difficult to sort, filter and display.
I have timestamps stored in the database as e.g.
I'm loading them (snippet below) and trying to format them for display.
Timestamp#(raised_time,'YYYY-MM-DD hh:mm:ss.fff') as raisedTimestamp,
Timestamp(Timestamp#(raised_time,'YYYY-MM-DD hh:mm:ss.fff'),'DD-MM-YYYY hh:mm:ss') as formattedTimestamp,
Timestamp(42716.356185243,'YY-MM-DD hh:mm:ss') as hardcoded_42716.356185243
When I put these values into a table, raisedTimestamp is displayed as a number (e.g. 42716.356185243), the hard-coded value displays as expected (12-12-2016 08:32:54) but the field I actually want, formattedTimestamp, is always null.
Any idea what I'm doing wrong? All the other posts seems to imply this is easy!
It's a mystery as the values returned by Timestamp# appear fine, and when I hard code one of the values into the Timestamp function all seems well. However a hard coded string also fails to convert correctly.
Looking at my last reply, I've noticed something else:
The Timestamp# is meant to interpret a string as a timestamp, but keep the original text:
Timestamp#('2016-12-12 08:32:54.405000','YYYY-MM-DD hh:mm:ss.ffffff') as hardcodedTimestamp
It does this: hardcodedTimestamp appears as '2016-12-12 08:32:54.405000' in the table. It's obviously been translated as a timestamp, as I can reformat it fine as formattedHardcodedTimestamp and it displays '12-12-2016 08:32:54'.
However, when I use Timestamp# on the loaded field raised_time and display it, the original text is not retained. The numerical value that should be behind the timestamp, 42716.356185243, is displayed instead. I'd have expected the raw string value of raised_time. Am I right here? If so, this tells me that the Timestamp# function has failed to interpret raised_time - But is somehow still parsing it as a timestamp and storing the numerical value.
I think you're right - Thanks for sticking with me.
I wasn't sure, as when I load in the same date/time data via excel (the values are stored as e.g. "13/12/2016 11:36:51"), Qlik is automatically spotting this is a date/time and expanding this in the "Fields" pane:
This isn't happening when loaded directly from the database where there is the milliseconds component. I've followed many threads where, in the end, everyone gives up and either changes the SQL that loads the data or changes the application that writes the data. I have control of both so will solve there.