2 Replies Latest reply: Nov 28, 2011 7:59 PM by John Witherspoon RSS

    Sorting by time



      I have a Straight table where one of many columns is Time:


      10:01 am

      10:02 am

      10:03 am

      ... and so on



      I want to be able to sort on time by clicking the Time column => but when I do, it sorts in a rather strange way, with no logic (at least it seems) in the sort.


      When I check the sort properties I find the choices to sort by by "text", "number/value", "frequency," and "Load order"...


      Could this have something to do with Time not being text or a number? I have tried sorting by both of them, but it does'nt sortthe way I want it to... Can anyone give some hints/directions for how I can handle this types of sorts?

        • Sorting by time
          Stefan Wühl



          you should check that you have read the Time values in as recognized time value. Either assure that the default format matches this time format code or foce QV to read the value as time value while loading, maybe like




          time#(TIMEFIELD,'hh:mm tt') as Time,


          from YOURTABLE;


          Please look into the Help for time#() function and the format codes. If you want to format the time values differently, please look also into time() function.


          The sorting should not be a problem with recognized time values with its numerical representation then.




            • Re: Sorting by time
              John Witherspoon

              You may be loading from a timestamp, and doing something like this:


              time(MyTimestamp) as MyTime


              It looks perfectly logical, but it also doesn't work, as time() is just a formatting function.  The underlying value is STILL a timestamp, and contains hidden date information.  If you sort numerically, your data would then be in "random" order because the sort is including the hidden date.  If that's what's going on, you'd want to load it more like this:


              time(frac(MyTimestamp)) as MyTime


              The frac() function will remove the date portion, leaving you with only a time, or more technically just the numeric equivalent of a time.  The time() function then formats that number as an actual time field.  A numeric sort will then work properly.