Potsgres SQL: select timestamp prior to max timestamp-CodePudding

I have a table in Postgres with timestamps:

timestamp
2022-01-01 00:52:53
2022-01-01 00:57:12
...
2022-02-13 11:00:31
2022-02-13 16:45:10

How can I select the timestamp closest to max timestamp? Meaning, I want the timestamp 2022-02-13 11:00:31.

I am looking for something like max(timestamp)-1 so I can do on a recurring basis. Thank you

CodePudding user response：

You can do:

select *
from (
  select *,
    rank() over(order by timestamp desc) as rk
  from t
) x
where rk = 2

See running example at DB Fiddle.

CodePudding user response：

I think the following query might meet your requirements:

SELECT MAX(date_col) FROM test WHERE date_col < (SELECT MAX(date_col) from test);