query to get data between date ranges and the padded data around-CodePudding

suppose this is my data in a table in the database

...
01/01/2016 00:00    367.2647688
01/06/2016 12:30    739.8067639 < INCLUDE THIS
01/01/2018 03:00    412.9686137
01/01/2018 03:30    150.6068046
01/01/2018 04:00    79.22204568
01/01/2018 04:30    648.702222
01/01/2018 09:00    75.41931365
01/01/2018 09:30    923.9435812
01/01/2018 10:00    342.9116004
02/01/2018 02:00    776.4855197 < INCLUDE THIS
08/04/2021 02:30    206.2066933
02/01/2022 03:00    852.9874735
02/01/2022 03:30    586.0818207
02/01/2022 04:00    363.5394613
02/01/2023 04:30    874.3073237
...

and this is my query to fetch data

SELECT * FROM MYTABLE WHERE [DATETIME] >= '2018/01/01 03:00' AND [DATETIME] < '2018/01/01 11:00'

I would also like the query to return one date before and after this range. so like the dates padded.

Now how can i do this efficiently. One way could be to get the dates in the ranges and then get all the data where they are less then min date and get the highest datetime of those and add to main range table also repeating this process for the max date. is there any other way?

CodePudding user response：

You can use lead() and lag():

SELECT *
FROM (SELECT t.*,
             LAG(DATETIME, 1, DATETIME) OVER (ORDER BY DATETIME) as PREV_DATETIME,
             LEAD(DATETIME, 1, DATETIME) OVER (ORDER BY DATETIME) as NEXT_DATETIME
      FROM MYTABLE t
     ) t
WHERE NEXT_DATETIME >= '2018-01-01 03:00:00' AND
      PREV_DATETIME <= '2018-01-01 10:00:00'

Note: This uses default values to simplify the logic.

Here is a db<>fiddle. Based on the results you specified, I changed the last comparison to <= from <.

CodePudding user response：

Lag and lead are window functions that are used to get preceding and succeeding value of any row within its partition. So when we want pad rows outside the range we can set offset parameter in both lag and lead functions.

SELECT t.Datetime,t.val FROM (SELECT T.*,LAG(Datetime,1,Datetime) over (order by Datetime) as lagdate,lead(Datetime,1,Datetime)over (order by Datetime) as leaddate FROM Mytable T)t
WHERE leaddate>= '2018/01/01 03:00' and lagdate<='2018/01/01 11:00'