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?
Advertisement
Answer
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 <.