CODEWITHSUNDEEP
postgresql
Greeny
Greeny

Reputation: 45

How to get count for every 1 hour interval

select
count("Status") as Total_Count

from "dbo"

where "Status" = 'Pass'

and "StartDateTime" BETWEEN '2020-11-01 15:00:00' AND '2020-11-01 16:00:00'

group by "Status"

enter image description here

How to get data for every 1 hour interval as in the image above? As currently i changing the time interval manualy. I want get the counts from 12am to 12am next day with 1 hour interval.

Upvotes: 3

Views: 1482

Answers (1)

S-Man
S-Man

Reputation: 23666

demo: db<>fiddle

When you truncate the start time with date_trunc() at the hour part, all times will be normalized to full hours. This can be used as the GROUP BY criterion.

SELECT
    COUNT(*)
FROM 
    t
GROUP BY date_trunc('hour', starttime)

To format the time column as you expect, you can use the to_char() function:

SELECT
    to_char(date_trunc('hour', starttime), 'HH12:MI:SS AM') || ' - ' || to_char(date_trunc('hour', starttime)  + interval '1 hour', 'HH12:MI:SS AM'),
    COUNT(*)
FROM 
    t
GROUP BY date_trunc('hour', starttime)

Upvotes: 4

Related Questions