CODEWITHSUNDEEP
sqlbusiness-intelligenceolapduckdb
Jaox
Jaox

Reputation: 100

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?

Upvotes: 1

Views: 1581

Answers (2)

Bob
Bob

Reputation: 1095

An easier way is to use the time_bucket function, for example:

SELECT
  time_bucket(INTERVAL '15m', createdat) AS time_range,
  SUM(tickets)
FROM counter
GROUP BY time_range
ORDER BY time_range;

Another example (which calls make_timestamp to convert ns since the epoch):

SELECT
  time_bucket(INTERVAL '1m', make_timestamp((created_at / 1000)::BIGINT)) AS time_range,
  SUM(tickets)
FROM 'out.csv'
GROUP BY time_range;

Upvotes: 6

Bohemian
Bohemian

Reputation: 425258

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.

Upvotes: 1

Related Questions