Question 90175 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search