sqlbusiness-intelligenceolapduckdb

Is there a way to group by intervals of 15 min in DuckDB?

I made a table with

create table counter (
  createdat TIMESTAMP,
  tickets INT, 
  id VARCHAR
)

and I would like to group the rows by intervals of 15 min, so I am trying to do it with:

SELECT
  SUM(tickets) AS total,
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat)
from counter
where id = ?
group by
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat)

With this query I am getting only by hour. However I cant modify it to minutes.

How would be the query to group by intervals of 15 minutes?

Solution

  • Add an expression for the quarter hour:

    (extract(minute from createdat) / 15)::integer

    to your columns:

    select
  sum(tickets) AS total,
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat),
  (extract(minute from createdat) / 15)::integer
from counter
where id = ?
group by
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat),
  (extract(minute from createdat) / 15)::integer

    Casting to integer truncates the fractional part of the division result.