groupbybin(date, binsize)

2 followers
0
Avatar

I have a question about the 2nd example of groupbybin in the following page:

https://www.datameer.com/documentation/display/DAS60/GROUPBYBIN

 

On the results column... how does the starting date of the first bin is decided?

I just changed 3d to 4d in the above example, and I noticed that the starting date on the results column changed from Jan 1, to Jan 2. 

Can you help me understand how this works?

Thanks!

 

Amin Torabi

4 comments

  • Avatar
    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
  • Avatar
    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
  • Avatar
    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
Please sign in to leave a comment.