getting rows and adding a column to each with the timestamp of the next row, with postgres

Question

I have the following object:

CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    id                   VARCHAR      NOT NULL,
    ticker               VARCHAR(16)  NOT NULL,
    interval             VARCHAR(3)   NOT NULL,
    ts                   TIMESTAMP    NOT NULL,
    ...
    UNIQUE (ticker, interval, ts)
);

I would like to do a query that will return rows like this:

SELECT * FROM analysis
WHERE ticker = 'BTCUSDT' AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

but, for each row, adds a column that contains the timestamp of the next row (sorted by ts).

Since I've only basic knowledge of SQL, I used ChatGPT to generate a solution, but it's insanely slow (as in minutes vs. milliseconds)

SELECT t1.ts as ts_start, t2.ts as ts_end, t1.ticker, t1.interval, ...
FROM analysis t1
JOIN analysis t2
ON t1.ticker = t2.ticker AND t1.interval = t2.interval AND t1.ts < t2.ts
WHERE t1.ticker = 'BTCUSDT' AND t1.ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY t1.ts;

What would be a better way to make this happen?

Answer 1

Use a window function:

SELECT *,
       lead(ts) over (partition by ticker order by ts) as next_ts 
FROM analysis
WHERE ticker = 'BTCUSDT' 
  AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

---

Note that using BETWEEN with timestamp values is usually a logical error. In your case, rows with a value of 2020-01-30 00:00:01 would not be selected because '2020-1-30' is converted to '2020-1-30 00:00:00' and the upper bound is compared with <= so that values after midnight on that day are excluded.

With timestamp values it's always better to use a range with >= and < where the upper bound is the next day

AND ts >= '2020-01-01' 
AND ts < '2020-01-31'

In fact if you intended to get all rows for January you would forget the last day as well, so maybe this is what you really wanted:

AND ts >= '2020-01-01' 
AND ts < '2020-02-01'