How to get count for every 1 hour interval

Question

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"

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.

Answer 1

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)