EN
Node.js - PostgreSQL- group rows by months
0 points
In this article, we would like to show you how to group rows by months in the Postgres database from Node.js level.
Note: at the end of this article you can find database preparation SQL queries.
xxxxxxxxxx1
const pg = require('pg');2
3
const types = pg.types;4
types.setTypeParser(1114, (stringValue) => {5
return stringValue;6
});7
8
const client = new pg.Client({9
host: '127.0.0.1',10
user: 'my_username',11
database: 'my_database',12
password: 'my_password',13
port: 5432,14
});15
16
const fetchEventsGroupedByMonths = async () => {17
const query = ` 18
SELECT19
DATE_TRUNC('month', "event_timestamp") AS "month",20
COUNT(*)21
FROM "events"22
GROUP BY DATE_TRUNC('month', "event_timestamp")23
`;24
await client.connect(); // creates connection25
try {26
const { rows } = await client.query(query); // sends query27
return rows;28
} finally {29
await client.end(); // closes connection30
}31
};32
33
fetchEventsGroupedByMonths()34
.then(result => console.table(result))35
.catch(error => console.error(error.stack));Result:
xxxxxxxxxx1
┌─────────┬───────────────────────┬───────┐2
│ (index) │ month │ count │3
├─────────┼───────────────────────┼───────┤4
│ 0 │ '2021-03-01 00:00:00' │ '5' │5
│ 1 │ '2021-04-01 00:00:00' │ '7' │6
└─────────┴───────────────────────┴───────┘Note:
In the example above, the
monthcolumn shows the date 1 day back (time zone-adjusted conversion), check the example below, which may be clearer for you.
xxxxxxxxxx1
const { Client } = require('pg');2
3
const client = new Client({4
host: '127.0.0.1',5
user: 'my_username',6
database: 'my_database',7
password: 'my_password',8
port: 5432,9
});10
11
const fetchEventsGroupedByMonths = async () => {12
const query = ` 13
SELECT 14
TO_CHAR(event_timestamp,'month') AS "month",15
EXTRACT(YEAR FROM event_timestamp) AS "year",16
COUNT(*)17
FROM "events"18
GROUP BY TO_CHAR(event_timestamp,'month'), EXTRACT(YEAR FROM event_timestamp) // or GROUP BY 1,219
`;20
await client.connect(); // creates connection21
try {22
const { rows } = await client.query(query); // sends query23
return rows;24
} finally {25
await client.end(); // closes connection26
}27
};28
29
fetchEventsGroupedByMonths()30
.then(result => console.table(result))31
.catch(error => console.error(error.stack));Result:
xxxxxxxxxx1
┌─────────┬─────────────┬──────┬───────┐2
│ (index) │ month │ year │ count │3
├─────────┼─────────────┼──────┼───────┤4
│ 0 │ 'march ' │ 2021 │ '5' │5
│ 1 │ 'april ' │ 2021 │ '7' │6
└─────────┴─────────────┴──────┴───────┘create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "events" (2
"id" SERIAL PRIMARY KEY,3
"event_timestamp" TIMESTAMP NOT NULL4
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "events" 2
("event_timestamp")3
VALUES4
('2021-03-11 11:41:31'),5
('2021-03-21 12:42:32'),6
('2021-03-22 14:44:34'),7
('2021-03-22 15:45:35'),8
('2021-03-23 16:46:36'),9
('2021-04-05 17:47:37'),10
('2021-04-05 19:49:39'),11
('2021-04-16 20:50:40'),12
('2021-04-17 15:45:35'),13
('2021-04-23 16:46:36'),14
('2021-04-23 19:49:39'),15
('2021-04-23 20:50:40');Native SQL query (used in the above example):
Example 1:
xxxxxxxxxx1
SELECT2
DATE_TRUNC('month', "event_timestamp") AS "month",3
COUNT(*)4
FROM "events"5
GROUP BY DATE_TRUNC('month', "event_timestamp")Example 2:
xxxxxxxxxx1
SELECT 2
TO_CHAR(event_timestamp,'month') AS "month",3
EXTRACT(YEAR FROM event_timestamp) AS "year",4
COUNT(*)5
FROM "events"6
GROUP BY TO_CHAR(event_timestamp,'month'), EXTRACT(YEAR FROM event_timestamp) // or GROUP BY 1,2