8 Replies Latest reply: Dec 15, 2016 5:53 AM by Sunny Talwar RSS

    Timestamp function always returning null

    Lee Curtis

      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.

        • Re: Timestamp function always returning null
          Sunny Talwar

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

            • Re: Timestamp function always returning null
              Lee Curtis

              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.

              alert-table.PNG

              alert-script.PNG

              Cheers,

              Lee

                • Re: Timestamp function always returning null
                  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

                   

                  Capture.PNG

                    • Re: Timestamp function always returning null
                      Lee Curtis

                      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.

                      alert-hardcoded.PNG

                       

                      Lee

                        • Re: Timestamp function always returning null
                          Lee Curtis

                          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.