PostgreSQL: count or sum for every 1 hour interval

Question

Hello I am using this query which gives me the results shown in the picture:

SELECT "t_stamp", "Total_Parts"
FROM myTable
WHERE "MachineName" = 'Main01'
AND "Quality" is not NULL
AND t_stamp >= '2023-08-10 16:30:00' AND t_stamp <= '2023-08-14 00:30:00'
ORDER BY 1 ASC

I wonder how can I get only the count for every hour... For example: I know I would have to sum the values from the Total_Parts column (in the red-box) but only for hour 17, and then the ones in the purple-box for the hour 18, and for the green-box for the hour 19.. Like the following table:

t_stamp	Total_Parts
2023-08-10 17:00	2,628
2023-08-10 18:00	2,016
2023-08-10 19:00	1,913

I was looking at this link, but still could not figured it out. Any help would be very much appreciate it. Thank you.

Answer 1

This works for me:

SELECT date_trunc('hour', t_stamp) AS t_stamp,
       SUM(Total_Parts)            AS Total_Parts
FROM myTable
WHERE "MachineName" = 'Main01'
      AND "Quality" IS NOT NULL
      AND t_stamp >= '2023-08-10 16:30:00'
      AND t_stamp <= '2023-08-14 00:30:00'
GROUP BY date_trunc('hour', t_stamp)
ORDER BY 1 ASC;

Oh, and, next time please add scripts for objects and data please :)