Get rows for a given time range, plus one preceding row

Question

I have a table in which I have queries that return data for a time range. The rows are sparse and events can be days apart.

I am looking for a way to get the data from the range + add the row before the range starts (which can be at any timestamp which is less than the start).

For example:

2020-01-01 xxxxxxx
2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx
2020-02-15 xxxxxxx

If I query from 2020-02-08 to 2020-02-14, I would get:

2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

But I would like to get:

2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

How can this be done?

Answer 1

This should be fastest and simplest:

(  -- parentheses required
SELECT *
FROM   tbl
WHERE  dt < '2020-02-08'
ORDER  BY dt DESC
LIMIT  1
)
UNION ALL
SELECT *
FROM   tbl
WHERE  dt BETWEEN '2020-02-08' AND '2020-02-14'
-- ORDER BY dt -- optional
;

fiddle

With an index on (dt) you get two very fast index scans.

If there can be duplicates on (dt), you'll have to define which preceding row to pick, or accept the arbitrary pick from the given query.

Use UNION ALL, not UNION, which would try to remove duplicates - which is either a waste of time or not what you want. UNION can also reorder result rows as a side effect.

About parentheses in UNION queries:

• Combining 2 SELECT queries and printing the results in PostgreSQL

This normally returns rows sorted by dt. But see:

• Is order preserved after UNION in PostgreSQL?
• Are results from UNION ALL clauses always appended in order?

So you may want to append ORDER BY dt as commented to guarantee sorted results.

Answer 2

One simple solution which will give the desired result , but for larger data you might have some performance issue.

To find the previous ,current value of the 2020-02-10 ,

with cte as (
SELECT  dt,
        LAG(dt,1) OVER ( ORDER BY dt asc ) AS previous_dt
FROM test_tbl 
  ) select previous_dt as dt from cte where  '2020-02-10' IN (dt,previous_dt);

Use union to combine with the range search,

with cte as (
SELECT  dt,
        LAG(dt,1) OVER ( ORDER BY dt asc ) AS previous_dt
FROM test_tbl 
  ) select previous_dt as dt from cte where  '2020-02-10' IN (dt,previous_dt)
    union 
    SELECT dt
    FROM test_tbl 
    WHERE dt >='2020-02-08' AND dt<='2020-02-14'  ;

Result,

   dt
2020-01-12
2020-02-11
2020-02-10

See example here