EN
Node.js - PostgreSQL Group rows by days
0 points
In this article, we would like to show you how in the group rows by days in 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('day', "event_timestamp") AS "day",20
COUNT(*)21
FROM "events"22
GROUP BY DATE_TRUNC('day', "event_timestamp")23
ORDER BY DATE_TRUNC('day', "event_timestamp")24
`;25
await client.connect(); // creates connection26
try {27
const { rows } = await client.query(query); // sends query28
return rows;29
} finally {30
await client.end(); // closes connection31
}32
};33
34
fetchEventsGroupedByMonths()35
.then(result => console.table(result))36
.catch(error => console.error(error.stack));Result:
xxxxxxxxxx1
┌─────────┬───────────────────────┬───────┐2
│ (index) │ day │ count │3
├─────────┼───────────────────────┼───────┤4
│ 0 │ '2021-03-11 00:00:00' │ '1' │5
│ 1 │ '2021-03-21 00:00:00' │ '1' │6
│ 2 │ '2021-03-22 00:00:00' │ '2' │7
│ 3 │ '2021-03-23 00:00:00' │ '1' │8
│ 4 │ '2021-04-05 00:00:00' │ '2' │9
│ 5 │ '2021-04-16 00:00:00' │ '1' │10
│ 6 │ '2021-04-17 00:00:00' │ '1' │11
│ 7 │ '2021-04-23 00:00:00' │ '3' │12
└─────────┴───────────────────────┴───────┘create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "events" (2
"id" SERIAL NOT NULL,3
"event_timestamp" TIMESTAMP NOT NULL,4
PRIMARY KEY ("id")5
);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):
xxxxxxxxxx1
SELECT2
DATE_TRUNC('day', "event_timestamp") AS "day",3
COUNT(*)4
FROM "events"5
GROUP BY DATE_TRUNC('day', "event_timestamp")6
ORDER BY DATE_TRUNC('day', "event_timestamp")