2 Replies Latest reply: Dec 18, 2015 12:22 AM by Peter Cech RSS

    Time range problems

      Hi,

      I am working on a qvw where I have to display time durations and time ranges.

      What I have available in the data source are a Start_Time field and an End_Time field in DD.MM.YYY hh:mm:ss

       

      So far I have successfully  created two duration fields by adding this to the script:

      Interval(Time(End_Time) - (Start_Time),'hh:mm:ss') as Duration

      Interval(Time(End_Time) - (Start_Time),'ss') as Duration_seconds

       

       

      No I want to create a time range field which should show the number of elements in a chart with durations of, let´s say

      less than 1 hour,

      1-3 hours,

      everything else

       

      I totally failed with the Duration field, that´s why I created the above Duration_Seconds field as well.

      My last useless effort looks like this:

       

      if((Duration_seconds)<=3600,'less than 1 hour',

        if((Duration_seconds)<=10800,'1-3hours',

        'everything else')) as Range

       

      The result is that all records fall into the less than 1 hour category, even if they lasted 8.000 seconds or more.

       

      I am lost...

      Is the seconds field in a wrong format? Can it be converted into numerical? Can it work with the Duration field?

       

      Thanks.

       

       

       

        • Re: Time range problems
          Bill Markham

          For your Duration_seconds try :

           

              (Time(End_Time) - (Start_Time))  * 24  * 60 * 60    as Duration_seconds


          A timestamp is held as the number of days since a certain date ages ago.  The integer part of it is the number of days since then and the decimal part is the fraction of a day.  It is held as a dual and the text representation is the standard human readable format.

           

          The 'ss' argument to your interval() formats the dual text representation to be human readable seconds, but the dual number part remains as a decimal.

           

          So to get the number of seconds multiply the decimal by the number of seconds in a day, which is 24*60*60..