Timestamp function always returning null

[#1]

Hi,

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.

2016-12-13 22:59:09.235

I'm loading them (snippet below) and trying to format them for display.

raised_time,

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!

Thanks in advance.

[sunny_talwar]

Would you be able to share you file or images of the front end and script?

[#3]

Hi Sunny,

Yep, sorry, pretty new to this. Hope this helps.

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.

Cheers,

Lee

[sunny_talwar]

Two things

1) I don't see .fff in your TimeStamp#() function

2) I see you have used HH:MM:SS at some places... the problem is that M is used for Months and m is used for minutes. I think replace HH:MM:SS with hh:mm:ss

[#5]

Many thanks.

You're right, I've been trying many combinations so see if I hit on the right one. I've tidied things up to try and illustrate the issue which still remains.

The raw raised time appears to have three trailing zeros, so I load it with ".ffffff" at the end.

Timestamp#(raised_time,'YYYY-MM-DD hh:mm:ss.ffffff') as raisedTimestamp

This converts to a timestamp OK with numeric value 42716.356185243. However when I try and format this for display null is always returned:

Timestamp(Timestamp#(raised_time,'YYYY-MM-DD hh:mm:ss.ffffff'),'DD-MM-YYYY hh:mm:ss') as formattedTimestamp

When I hard code the numeric value into a timestamp function it formats OK:

Timestamp(42716.356185243,'DD-MM-YYYY hh:mm:ss') as hardcoded_42716.356185243

Finally, I hard coded the raw string value of raised_time into the functions and they both convert and format fine:

Timestamp#('2016-12-12 08:32:54.405000','YYYY-MM-DD hh:mm:ss.ffffff') as hardcodedTimestamp,

Timestamp(Timestamp#('2016-12-12 08:32:54.405000','YYYY-MM-DD hh:mm:ss.ffffff'),'DD-MM-YYYY hh:mm:ss') as formattedHardcodedTimestamp

So this has got me very confused. How can both timestamp functions process the hard coded values, but when using an actual field it fails? This can't be a bug - I'm missing something!

The screenshot below shows the output.

Lee

[#6]

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.

Confused.

[sunny_talwar · Accepted solution]

From this image, it seems that raised time is already interpreted correctly by Qlik Sense and you might not really need to use TimeStamp#() function. Can you just try this:

TimeStamp(raised_time,'DD-MM-YYYY hh:mm:ss') as raisedTimestamp

[#8]

Hi Sunny,

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.

Thanks again!

[sunny_talwar]

Sounds good