TIME_BUCKET group by time period

We all know how to sum up by year

select
  to_char(trunc(hiredate,'Y'),'YYYY') year,
  count(*)
from
  scott.emp
group by
  trunc(hiredate,'Y')
order by 
  trunc(hiredate,'Y')

YEAR COUNT(*)
---- --------
1980        1
1981       10
1982        1
1987        2

This is quite easy. Same for day (DD), century (CC), quartal (Q), hour (HH24), month (MM)

But how do you group by, let’s say, half-year? or fiscal year, half day, 8-hours period, etc… ?

One way is to convert to number…

select 
  replace(
    replace(
      to_char(
        trunc(
          months_between(
            hiredate, 
            date '1900-01-01'
          )/6
        )/2,
        '00.0'
      ),
    '.0','H1'
  ),'.5','H2') HY, 
  COUNT(*) 
from 
  scott.emp 
group by 
  trunc(months_between(hiredate, date '1900-01-01')/6) 
order by 
  trunc(months_between(hiredate, date '1900-01-01')/6);

HY         COUNT(*)
------- -----------
80H2              1
81H1              5
81H2              5
82H1              1
87H1              2

We could also add some offset to start in March and choose 180 days instead of 6 months etc… But it gets really hard to read (and write)

In Oracle 23.7, I could not test it yet, simply write …

select
  time_bucket(hiredate, interval '6' month, date '1900-01-01'),
  count(*)
from
  scott.emp
group by
  time_bucket(hiredate, interval '6' month, date '1900-01-01')

It is much nicer 🙂