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

I have a table that has some file input data with file_id, 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.

I am not a pro in postgres. Is there any way to do that?

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.

CodePudding user response：

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')

CodePudding user response：

To get to hour not minute:

create table date_grp (file_id integer, file_input_date timestamp);
INSERT INTO date_grp VALUES
(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');

SELECT
    date_trunc('hour', file_input_date),
    count(date_trunc('hour', file_input_date))
FROM
    date_grp
GROUP BY
    date_trunc('hour', file_input_date);

 date_trunc      | count 
--------------------- -------
 01/11/2023 18:00:00 |     1
 01/11/2023 17:00:00 |     3
 01/12/2023 16:00:00 |     1
 01/11/2023 16:00:00 |     2
(4 rows)

Though if you want to minute

SELECT
    date_trunc('minute', file_input_date),
    count(date_trunc('minute', file_input_date))
FROM
    date_grp
GROUP BY
    date_trunc('minute', file_input_date);

 date_trunc      | count 
--------------------- -------
 01/11/2023 18:06:00 |     1
 01/11/2023 16:06:00 |     2
 01/12/2023 16:06:00 |     1
 01/11/2023 17:06:00 |     3