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 >> >> >
