EN
Node.js - PostgreSQL Find rows created within the last hour / hours
0 points
In this article, we would like to show you how to find rows created within the last hours 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 pg = require('pg');4
5
const types = pg.types;6
types.setTypeParser(1114, (stringValue) => {7
return stringValue;8
});9
10
const client = new Client({11
host: '127.0.0.1',12
user: 'my_username',13
database: 'my_database',14
password: 'my_password',15
port: 5432,16
});17
18
const fetchEventsFromLastHours = async (hourCount) => {19
const query = ` 20
SELECT *21
FROM "events"22
WHERE "event_timestamp" > NOW() - $1 * interval '1 hour';23
`;24
await client.connect(); // creates connection25
try {26
const { rows } = await client.query(query, [hourCount]); // sends query27
return rows;28
} finally {29
await client.end(); // closes connection30
}31
};32
33
fetchEventsFromLastHours(2) // enter the number of hours you want to get results34
.then(result => console.table(result))35
.catch(error => console.error(error.stack));In the example above, the code runs on 4/23/2021 21:00:00, so the results are:
xxxxxxxxxx1
┌─────────┬────┬───────────────────────┐2
│ (index) │ id │ event_timestamp │3
├─────────┼────┼───────────────────────┤4
│ 0 │ 11 │ '2021-04-23 19:49:39' │5
│ 1 │ 12 │ '2021-04-23 20:50:40' │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):
xxxxxxxxxx1
SELECT *2
FROM "events"3
WHERE "event_timestamp" > NOW() - 2 * interval '1 hour';