CODEWITHSUNDEEP
sqlpostgresqltime
Ninja
Ninja

Reputation: 301

Get daily count of rows for a Time

My database table looks like this:

CREATE TABLE record 
(
    id         INT,
    status     INT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

And I want to create a generic query to get count of record created after 3 hours of interval in last day

For example, I want to know in last 1 day after 3 hours how many records are created.

What I have so far: with a little help from stackoverflow I am able to create a query to calculate the count for a single full day.

SELECT
    DATE(created_at) AS day, COUNT(1)
FROM
    record
WHERE
    created_at >= current_date - 1
GROUP BY
    DATE(created_at)

This is telling me in full day like 24 records are created but I want to get how many are made in interval of 3 hours

Upvotes: 0

Views: 304

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want the count for the last three hours of data:

select count(*)
from record
where created_at >= now() - interval '3 hour';

If you want the last day minus 3 hours, that would be 21 hours:

select count(*)
from record
where created_at >= now() - interval '21 hour';

EDIT:

You want intervals of 3 hours for the last 24 hours. The simplest method is probably generate_series():

select gs.ts, count(r.created_at)
from generate_series(now() - interval '24 hour', now() - interval '3 hour', interval '3 hour') gs(ts) left join
     record r 
     on r.created_at >= gs.ts and
        r.created_at < gs.ts + interval '3 hour'
group by gs.ts
order by gs.ts;

Upvotes: 1

Related Questions