3 Replies Latest reply: Jan 17, 2013 10:58 AM by Stefan Wühl RSS

    Time difference in decimal format.

    andries Bos

      I have two day time values within a database and want to create a decimal calculated value as a new field within my script to show the total hours in a decimal format.

       

      time1   20120815153000

      time2   20120815070000

      Format:YYYYMMDDHHMMSS

       

      Within this example my end time is 15h30 and my starting time is 07h30. I would like to have an integer value of 8.

       

      A second attempt would be to also use the amount of day's:

      (Left(Dte,8) - Left(Dts,8) + 1) * ((Dte - Dts)/100) AS IZ.TotaalHours,

      This works except for the hour part.

        • Re: Time difference in decimal format.
          Stefan Wühl

          First, inteprete your datetime values correctly using e.g. timestamp#() function.

          Then you can calculate your time difference using interval() function.

           

          LOAD *,

               num#(Interval(time1-time2,'h')) as DurationHours;

          LOAD timestamp(Timestamp#(time1,'YYYYMMDDhhmmss')) as time1,

               timestamp(Timestamp#(time2,'YYYYMMDDhhmmss')) as time2

          INLINE [

          time1, time2  

          20120815153000,20120815070000

          ]

          • Re: Time difference in decimal format.
            andries Bos

            I actually managed to solve my problem in a rather easy way with your suggested timestamp:

             

            This could be done within the original scripting without an extra load.

             

            Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) * 24, 0.1)

             

            This will format my times, will subtract them and convert them into a number: multiplication with 24 will effectly resolve it into a needed decimal number.

             

            As these are quite large numbers, the round function for this example will round the results into the needed 8,5 hours.

             

            A second stage could be to calculate the amount of days. using ony the 8 left characters and multiply this with the hours / day.

              • Re: Time difference in decimal format.
                Stefan Wühl

                Just reuse the timestamp difference (the integer part is giving the number of days):

                 

                Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

                 

                to get the number of days rounded or

                 

                floor(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

                 

                to get the number of days, only full days counted.