CODEWITHSUNDEEP
postgresql
Santhosh
Santhosh

Reputation: 11788

postgresql: how to get the rows greater than a timestamp and also previous nearest row

I have postgresql table with time and state

      time              state
2021-01-30 09:59:57+00        0
2021-01-30 09:50:36+00        1
2021-01-30 08:38:05+00        0
2021-01-30 08:29:37+00        1
2021-01-30 08:05:16+00        0
2021-01-30 07:42:27+00        1

i am looking for rows greater than 2021-01-30 07:45:00+00 but also want to have the nearest row before also. In this case 2021-01-30 07:42:27+00 similary w.r.t to less than

any option to include the previous one row near to the condition

Upvotes: 3

Views: 2600

Answers (1)

Belayer
Belayer

Reputation: 14861

Use the Window variant of MAX function and union that with standard select. (see fiddle)

select time_ts "Time", state  
  from test
 where time_ts >= '2021-01-30 07:45+00'::timestamp
union
Select * 
  from (select max (time_ts) over(), state
          from test
         where time_ts < '2021-01-30 07:45+00'::timestamp  
         limit 1
       ) f
order by 1;

In Reply to " max (time_ts) over() can you explain what it does "
First see Documentation on Window functions. Max is one of the Aggregate functions that also has a Window variant. Within Window functions the Over(...) builds groups (partitions) within row set selected, using Over() clause creates a single group over the entire row set. The advantage here is that is avoids the "Group By" required by the standard aggregate (which could produce multiple rows). While selecting a single row it will select the same row for each row in the result set thus the limit 1. You can see this with the following:

select time_ts, max(time_ts) over() from test;

Note: You used TIME as a column name, but that is a valid Postgres data type. I do not use data types as column names. I get confused over talking about the column or the data type.

Upvotes: 1

Related Questions