EN
Node.js - PostgreSQL Find rows between two timestamps
0 points
In this article, we would like to show you how to find rows between two absolute timestamps 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 fetchEventsBetween = async (fromDate, toDate) => {17
const query = ` 18
SELECT *19
FROM "events"20
WHERE "event_timestamp" BETWEEN $1 AND $221
`;22
await client.connect(); // creates connection23
try {24
const { rows } = await client.query(query, [fromDate, toDate]); // sends query25
return rows;26
} finally {27
await client.end(); // closes connection28
}29
};30
31
fetchEventsBetween('2021-03-22', '2021-04-17')32
.then(result => console.table(result))33
.catch(error => console.error(error.stack));Result:
xxxxxxxxxx1
┌─────────┬────┬───────────────────────┐2
│ (index) │ id │ event_timestamp │3
├─────────┼────┼───────────────────────┤4
│ 0 │ 3 │ '2021-03-22 14:44:34' │5
│ 1 │ 4 │ '2021-03-22 15:45:35' │6
│ 2 │ 5 │ '2021-03-23 16:46:36' │7
│ 3 │ 6 │ '2021-04-05 17:47:37' │8
│ 4 │ 7 │ '2021-04-05 19:49:39' │9
│ 5 │ 8 │ '2021-04-16 20:50:40' │10
└─────────┴────┴───────────────────────┘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" BETWEEN '2021-03-22' AND '2021-04-17'