Search code examples
sqlpostgresqlselectsubquery

postgres query to group the records by hourly interval with date field

I have a table that has some file input data with file_id and file_input_date. I want to filter / group these file_ids depending on file_input_date. The problem is my date is in format of YYYY-MM-DD HH:mm:ss and I want to go further to group them by hour and not just the date.

Edit: some sample data

file_id | file_input_date
597872  | 2023-01-12 16:06:22.92879
497872  | 2023-01-11 16:06:22.92879
397872  | 2023-01-11 16:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 17:06:22.92879
297872  | 2023-01-11 18:06:22.92879

what I want to see is

1 for 2023-01-12 16:06
2 for 2023-01-11 16:06
3 for 2023-01-11 17:06
1 for 2023-01-11 18:06

the output format will be different but this kind of gives what I want.

Solution

  • You could convert the dates to strings with the format you want and group by it:

    SELECT   TO_CHAR(file_input_date, 'YYYY-MM-DD HH24:MI'), COUNT(*)
FROM     mytable
GROUP BY TO_CHAR(file_input_date, 'YYYY-MM-DD HH24:MI')