7 Replies Latest reply: Feb 7, 2014 6:44 AM by Christian Riffner RSS

    Rounding time

      I need some help on rounding. I have some values that I want to round either to the closest hour or closest half hour.

       

      for example: (This is a 24 hour clock, not 12)

       

       

       

       

      00.28.26 should be 00.30.00

      07.58.26 should be 08.00.00

      19.58.26 should be 20.00.00

      20.29.00 should be 20.30.00

       

       

      Any idea how to go about this?

      My field with the time data is called "Metrics Time"

        • Re: Rounding time
          Stefan Wühl

          I assumed you have set your time format accordingly and your field is interpreted as time value.

           

          Try

           

          LOAD

          [Metrics Time],

          time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime,

          ...

          FROM ...;

           


            • Re: Rounding time

              Swuehl to the rescue! As always!

               

              That function loads but the new field doesn't have any data.

               

              Perhaps I haven't done the correct formatting?

                • Re: Rounding time
                  Stefan Wühl

                  If you haven't set the correct standard time format, your field is probably not correctly interpreted, try

                   

                  Try

                   

                  LOAD *,

                  time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime;

                  LOAD

                  time#([Metrics Time],'hh.mm.ss') as [Metrics Time]

                  ...

                  FROM ...;

                   

                  If you are coping with intervals rather than time values (i.e. values can exceed 24h), use the interval#() / interval() functions.

                   

                  Hope this helps,

                  Stefan

                    • Re: Rounding time

                      I guess im kind of confused as to where im putting all this...

                       

                       

                      You'd think I would have gotten it by now.

                        • Re: Rounding time
                          Stefan Wühl

                          If above is your general format you want to use as time format, use

                           

                          SET TimeFormat='hh.mm.ss';

                           

                          at the top of your script. If so, I would recommend to use the standard format.

                           

                          If you interpret a time format using time#() function, this will only affect the field you are applying this function to ([Metrics Time2] in your example). So if you use TIME again in the calculation for the rounded value, the format is not interpreted with the 'special' format code. You can do a preceding load like I've shown above or just embed the format interpretation into the round:

                           

                          time(round( time#([Time],'hh.mm.ss'),maketime(0,30))) as RoundedMetricsTime;

                  • Re: Rounding time
                    Christian Riffner

                    Hey guys,

                     

                    to round the time is also very important to show the correct interval value in the format hh:mm:ss. I've had the problem, that QV shows the floor-value of an interval.

                     

                    e.g.:

                     

                    0,00024722223 = 00:05:00

                    0,00024722221 should be rounded to 00:05:00, but QV shows 00:04:59

                     

                    By using the expression round(value,maketime(0,0,1)), QV rounds the value to the nearest full second. In my example it is 00:05:00.

                     

                    Bye,

                    Riffi