Selecting a time range, but include the row before the range starts

Question

Given the following table:

CREATE TABLE balance (
  confirmed_at TIMESTAMP WITH TIME ZONE NOT NULL,
  value BIGINT
);

I want to select all rows where confirmed_at is between start and end, however, I would also like the row that exists right before the time range. That way if there are no rows between the range, I still know the last value that was entered.

Is there a way to do this in a single query?

Answer 1

(  -- parentheses required
SELECT *
FROM   balance
WHERE  confirmed_at BETWEEN $start AND $end
ORDER  BY confirmed_at DESC  -- optional
)
UNION ALL
(  -- parentheses required
SELECT *
FROM   balance
WHERE  confirmed_at < $start
ORDER  BY confirmed_at DESC
LIMIT  1
);

You need additional ORDER BY items to get a deterministic pick, since confirmed_at is not UNIQUE. (Or you are OK with an arbitrary pick as is.)

If the table is not trivially small, be sure to have an index on (confirmed_at).

Parentheses are required to apply ORDER BY and LIMIT locally. See:

• Combine multiple SELECT statements