You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Stefán Baxter <st...@activitystream.com> on 2015/07/15 20:59:30 UTC
Rrounding timestamps to nearest period interval
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
Re: Rrounding timestamps to nearest period interval
Posted by Stefán Baxter <st...@activitystream.com>.
Thank you Christopher I will build my "workaround" on this.
-Stefan
On Wed, Jul 15, 2015 at 8:49 PM, Christopher Matta <cm...@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 cmatta@mapr.com
> 215-701-3146
>
> On Wed, Jul 15, 2015 at 4:33 PM, Stefán Baxter <
> stefan@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.mehant@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
> > >>
> > >>
> > >
> >
>
>
Re: Rrounding timestamps to nearest period interval
Posted by Christopher Matta <cm...@mapr.com>.
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 cmatta@mapr.com
215-701-3146
On Wed, Jul 15, 2015 at 4:33 PM, Stefán Baxter < stefan@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.mehant@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
> >>
> >>
> >
>
Re: Rrounding timestamps to nearest period interval
Posted by Stefán Baxter <st...@activitystream.com>.
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 <ba...@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
>>
>>
>
Re: Rrounding timestamps to nearest period interval
Posted by Mehant Baid <ba...@gmail.com>.
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
>