Node.js - PostgreSQL Find rows between two timestamps

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.

const pg = require('pg');

const types = pg.types;
types.setTypeParser(1114, (stringValue) => {
    return stringValue;
});

const client = new pg.Client({
    host: '127.0.0.1',
    user: 'my_username',
    database: 'my_database',
    password: 'my_password',
    port: 5432,
});

const fetchEventsBetween = async (fromDate, toDate) => {
	const query = `    
            SELECT *
            FROM "events"
            WHERE "event_timestamp" BETWEEN $1 AND $2
    `;
    await client.connect();  // creates connection
    try {
        const { rows } = await client.query(query, [fromDate, toDate]);  // sends query
        return rows;
    } finally {
        await client.end();  // closes connection
    }
};

fetchEventsBetween('2021-03-22', '2021-04-17')
    .then(result => console.table(result))
    .catch(error => console.error(error.stack));

Result:

┌─────────┬────┬───────────────────────┐
│ (index) │ id │    event_timestamp    │
├─────────┼────┼───────────────────────┤
│    0    │ 3  │ '2021-03-22 14:44:34' │
│    1    │ 4  │ '2021-03-22 15:45:35' │
│    2    │ 5  │ '2021-03-23 16:46:36' │
│    3    │ 6  │ '2021-04-05 17:47:37' │
│    4    │ 7  │ '2021-04-05 19:49:39' │
│    5    │ 8  │ '2021-04-16 20:50:40' │
└─────────┴────┴───────────────────────┘

Database preparation

create_tables.sql file:

CREATE TABLE "events" (
	"id" SERIAL PRIMARY KEY,
	"event_timestamp" TIMESTAMP NOT NULL
);

insert_data.sql file:

INSERT INTO "events" 
	("event_timestamp")
VALUES
    ('2021-03-11 11:41:31'),
    ('2021-03-21 12:42:32'),
    ('2021-03-22 14:44:34'),
    ('2021-03-22 15:45:35'),
    ('2021-03-23 16:46:36'),
    ('2021-04-05 17:47:37'),
    ('2021-04-05 19:49:39'),
    ('2021-04-16 20:50:40'),
    ('2021-04-17 15:45:35'),
    ('2021-04-23 16:46:36'),
    ('2021-04-23 19:49:39'),
    ('2021-04-23 20:50:40');

Native SQL query (used in the above example):

SELECT *
FROM "events"
WHERE "event_timestamp" BETWEEN '2021-03-22' AND '2021-04-17'