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 [email protected]
215-701-3146
On Wed, Jul 15, 2015 at 4:33 PM, Stefán Baxter < [email protected]>
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 < [email protected]>
> 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
> >>
> >>
> >
>
