Next line data?

Question

PostgreSQL

Hello.

There is a query that displays 4 lines:

select date, open_close from moex_fin m
where open_close BETWEEN 3.15 * 0.985 AND 3.15 * 1.005;

The task is to display next the values ​​of the next day. The table is the same.
I can't figure out what's wrong, it only outputs two values ​​instead of 4.

select m.date, m.open_close, mo.date, mo.open_close from moex_fin m
  JOIN moex_fin mo ON (m.date + 1 = mo.date)
  where m.open_close BETWEEN 3.15 * 0.985 AND 3.15 * 1.005;

Tell me who knows.
Thank you in advance.

Answer 1

PostgreSQL provides function `lead` for this
case

with d as (
  select 
    date, 
    open_close,
    lead(date) over (order by date asc) next_date,
    lead(open_close) over (order by date asc) next_open_close
  from moex_fin m
) select * from d
where open_close BETWEEN 3.15 * 0.985 AND 3.15 * 1.005
;

PostgreSQL lead window function

Answer 2

Because you have JOIN, and if there is nothing to connect from that side, then it doesn’t output from this one either.