groupbybin(date, binsize)

Comments

4 comments

  • Alan Mark

    Hi Amin,

    GROUPBYBIN has difficulties dealing with some dates based on the underlying math.

    Our suggested method for binning dates is to convert a timestamp into a formated date that you can group on.

    E.G. If you want to have bins that represent 1 day each, you should use this:

    GROUPBY(FORMATDATE(#date;"yyyy-MM-dd"))

    Alan

    0
    Comment actions Permalink
  • Amin Torabi

     

    Hi Alan,

    You suggested method works fine only if my bin size is 1d.

    What would you suggest if my bin size is 2d? I don't think GROUPBY can replace GROUPBYBIN in this case.

     

    0
    Comment actions Permalink
  • Alan Mark

    Hi Amin,

    One way to achieve this would be to expose the EPOCH time with the TIMESTAMP function.  This would allow you to operate directly on milliseconds with GROUPBYBIN.  

    MSTODATE(GROUPBYBIN(TIMESTAMP(#date);172800000))

    Where 172800000 is the number of milliseconds in 2 days.

    Please note GROUPBYBIN was not intended to be used with dates, only numerics.   I've opened an internal documentation ticket to have the examples corrected.

    Alan

    0
    Comment actions Permalink
  • Amin Torabi

    Awesome! Thanks Alan! 

    0
    Comment actions Permalink

Please sign in to leave a comment.