EN
Node.js - PostgreSQL Find rows created within the last month / months
0 points
In this article, we would like to show you how to find rows created within the last 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 { 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 fetchEventsFromLastMonths = async (monthCount) => {12
const query = ` 13
SELECT *14
FROM "events"15
WHERE "event_timestamp" > NOW() - $1 * interval '1 month';16
`;17
await client.connect(); // creates connection18
try {19
const { rows } = await client.query(query, [monthCount]); // sends query20
return rows;21
} finally {22
await client.end(); // closes connection23
}24
};25
26
fetchEventsFromLastMonths(1)27
.then(result => console.table(result))28
.catch(error => console.error(error.stack));Result:
xxxxxxxxxx1
┌─────────┬────┬──────────────────────────┐2
│ (index) │ id │ event_timestamp │3
├─────────┼────┼──────────────────────────┤4
│ 0 │ 6 │ 2021-04-05T15:47:37.000Z │5
│ 1 │ 7 │ 2021-04-05T17:49:39.000Z │6
│ 2 │ 8 │ 2021-04-16T18:50:40.000Z │7
│ 3 │ 9 │ 2021-04-17T13:45:35.000Z │8
│ 4 │ 10 │ 2021-04-23T14:46:36.000Z │9
│ 5 │ 11 │ 2021-04-23T17:49:39.000Z │10
│ 6 │ 12 │ 2021-04-23T18:50:40.000Z │11
└─────────┴────┴──────────────────────────┘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):
xxxxxxxxxx1
SELECT *2
FROM "events"3
WHERE "event_timestamp" > NOW() - $1 * interval '1 month';