Thank you Christopher I will build my "workaround" on this. -Stefan
On Wed, Jul 15, 2015 at 8:49 PM, Christopher Matta <cma...@mapr.com> wrote: > I did this last week with a query on the Drill profiles, this query will > break down the number of queries in 5 minute increments: > > select x.`timestamp`, x.`user`, count(1) from ( > select > t.`user`, > to_timestamp(((cast(t.`start` as bigint)/1000) - > MOD((cast(t.`start` as bigint)/1000), 300))) as `timestamp` > from maprfs.profiles.`profiles` t > WHERE to_timestamp(cast(t.`start` as bigint)/1000) > > DATE_SUB(NOW(), interval '1' month) > ) x > group by x.`timestamp`, x.`user` > order by x.`timestamp` asc > > I’m of course starting with BIGINT unix timestamps, so you’ll have to > convert using unix_timestamp(). > > Chris Matta cma...@mapr.com > 215-701-3146 > > On Wed, Jul 15, 2015 at 4:33 PM, Stefán Baxter < > ste...@activitystream.com> > wrote: > > Hi, > > > > This is used to group on time periods (minutes, hour of day, > > four-hour-groups-per-day etc). > > > > Ceiling to nearest 15 minutes interval (PT15M) then Looking at time > without > > mills: > > > > 2015-07-15T00:07:00 becomes 2015-07-15T00:15:00 > > 2015-07-15T00:16:00 becomes 2015-07-15T00:30:00 > > 2015-07-15T01:59:00 becomes 2015-07-15T02:00:00 > > > > A common SQL statement might look like this: > > "select dateadd(minute, datediff(minute,0, the_timestamp) / 15 * 15, 0) > > from ..." > > > > A UDF might look something like this: DATE_PERIOD(the_timestamp, > > the_period) -> DATE_PERIOD(timestamp '2015-07-15T01:59:00', 'PT15M') -> > > 2015-07-15T02:00:00 > > - (I have really not given this much thought and there must be people > here > > better suited to suggest interface for this than I am :) ) > > > > This way it's easy to break results down by what ever 8601 time (PT) > > period. > > > > Regards, > > - Stefan > > > > > > On Wed, Jul 15, 2015 at 7:57 PM, Mehant Baid < baid.meh...@gmail.com> > > wrote: > > > > > Hey Stefan, > > > > > > Could you clarify with an example what is the input and expected output > > > for the UDF you are looking for. > > > > > > Thanks > > > Mehant > > > > > > > > > On 7/15/15 11:59 AM, Stefán Baxter wrote: > > > > > >> Hi, > > >> > > >> I don't seem to find a handy way to round timestamps to nearest period > > >> interval (PT5M / PT15M) and DATE_DIFF seems to be missing for simple > > >> calculation of it. > > >> > > >> It seems like a too common use case for me to write a UDF for it but > if > > >> it's missing then we will happily contribute a simple implementation. > > >> > > >> How is this generally being solved by Drill users? > > >> > > >> Regards, > > >> -Stefan > > >> > > >> > > > > > > >
