2 Replies Latest reply: May 7, 2017 4:35 PM by Stefan Wühl RSS

    Calculate the time in hours between 2 dates

    Mahendra Sarkar

      Hi

      I am new to QlikView. I am preparing a report where I need to calculate the time between two dates to be converted in hours.

      How do I go about writing the script.

       

      Regards

       

      Mahendra

        • Re: Calculate the time in hours between 2 dates
          Stefan Wühl

          Dates / timestamps in Qlik are stored as dual values, i.e. they have an underlying numeric values (integers denoting days since Dec 30th, 1899, fraction of decimals denoting time of the day, i.e. 0.25 represents 6 AM) and a text representation (showing the date / timestamp in the format you want).

           

          So it's important to ensure your dates / timestamps are read in correctly when you load your data:

          Get the Dates Right

          Why don’t my dates work?

           

          Then it's easy to calculate the difference between two timestamps, you can just subtract the values:

           

          LOAD

               EndTimestampField - StartTimestampField as Duration

               EndTimestampField,

               StartTimestampField,

               ...

          FROM ..;

           

          This will show a number, which you then can format as you like using Interval() function:

           

          Interval( EndTimestampField - StartTimestampField, 'hh:mm:ss') as Duration