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
>Solution :
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.