groupbybin(date, binsize)

Follow

Amin Torabi

• July 04, 2017 19:40

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!

 

0

• 

  Alan

  • July 05, 2017 14:44

  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

  • July 05, 2017 15:06

   

  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

  • July 05, 2017 15:46
  • Edited

  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

  • July 05, 2017 16:09

  Awesome! Thanks Alan! 

  0

  Comment actions Permalink

Please sign in to leave a comment.