1

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?

Bohemian
  • 412,405
  • 93
  • 575
  • 722
Jaox
  • 90
  • 1
  • 9

1 Answers1

2

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thanks a lot!! is this the better query to get intervals by 15 mins or is there other queries with better performance? – Jaox Jul 16 '21 at 04:14
  • 1
    @jaox this is the only way AFAIK and it will perform quite well - simple arithmetic operations like this are pretty fast – Bohemian Jul 16 '21 at 04:26