PostgreSQL - query for record that is either side of the result set-CodePudding

Lets say I have this table (balances) schema and data:


 ---- --------- ------------ 
| id | balance | createdAt  |
 ---- --------- ------------ 
|  1 |      10 | 2021-11-18 |
|  2 |      12 | 2021-11-16 |
|  3 |       6 | 2021-11-04 |
 ---- --------- ------------

To retrieve the last 7 days of balances, I would do something like this:

SELECT * FROM "balances" WHERE "createdAt" BETWEEN '2021-11-19T09:04:17.488Z' AND '2021-11-12T10:04:17.488Z' ORDER BY "createdAt" ASC

This will give me 2 records (IDs: 1 & 2), which is fine. However, what I'm looking at doing, probably with a second query, is to grab the record that is previous to that result set, by createdAt date, as my query is ordered by createdAt. Is there a way to do this with PG?

So whatever the time-range I use, I would also retrieve the record that is n-1 to the result set

CodePudding user response：

To obtain the record you want, you may use a LIMIT query:

SELECT *
FROM balances
WHERE createdAt < '2021-11-19T09:04:17.488Z'
ORDER BY createdAt DESC
LIMIT 1;

This answer makes an assumption that there is only one record which is logically earlier than 2021-11-19T09:04:17.488Z, and there is no edge case of ties. If there are ties, we can break them by adding more levels to the ORDER BY clause.