SQL to Group or Chunk Date Time Fields by n Minutes

Posted by Michael Nitschke on Tuesday, 26 November 2013
/ Comments: (0)
Say you want to chunk some statistics data into 10 minute intervals. as of Oracle 11g you can use the extract() function that will pull out hours, minutes and the like from a DateTime column.
select
  to_date(trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0'), 'dd-mon-yy hh24:mi') as dttm_stamp
, count(some_field) "count"
from 
  ps_statistics_table
group by
  trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0')
;

Note the +0.1 bit that handles the range of results, up to that point spanning from 0 to 6. We can't have nn:60 as a vaild DateTime. This is also why we have the -10 part.

Also note that depending on the platform that is actually executing the SQL you may have to fiddle around with the DateTime formats. 'dd-mon-yy hh24:mi' might not cut it for you. PeopleSoft will require 'yyyy-mm-dd hh24:mi' for example.

A breakdown of the entire line:
select
  dttm_stamp as a
, extract(minute from dttm_stamp) as b
, ceil(extract(minute from dttm_stamp)/10 + 0.1)  as c
, ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10 as d
, lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as e
, trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as dttm_stamp
from ps_statistics_table
;

The final results from the first query: 
17/11/2013 10:00:00 AM 99
17/11/2013 10:10:00 AM 120
17/11/2013 10:20:00 AM 50
17/11/2013 10:30:00 AM 10
17/11/2013 10:40:00 AM 65
17/11/2013 10:50:00 AM 77
17/11/2013 11:00:00 AM 189
17/11/2013 11:10:00 AM 201
17/11/2013 11:20:00 AM 235
17/11/2013 11:30:00 AM 188
17/11/2013 11:40:00 AM 105
17/11/2013 11:50:00 AM 77