How do I know that these two dates are in the range of start date and end date?

for example, I have data in the database as follows:

Input:
start_date : '2022-11-07 08:00:00'
end_date : '2022-11-07 09:00:00'

if I input the data above, it means the data is not in the range right? but when I use this query, the data is still in the range

SELECT
    start_date,
        end_date,
FROM
    table
WHERE
    (start_date >= '2022-11-07 08:00:00' AND start_date <= '2022-11-07 09:00:00')
    OR 
    (end_date >= '2022-11-07 08:00:00' AND end_date <= '2022-11-07 09:00:00')

Would you like to help me? Thank you in advance.

I want to get the right query for range between two dates

CodePudding user response：

The easiest way to deal with ranges is to use a range type

select *
from the_table
where tsrange(start_date, end_date) @> tsrange('2022-11-07 08:00:00', '2022-11-07 09:00:00');

The @> is the "contains" operator and is true if the left hand range completely includes the range on the right hand side.

The above will create ranges that exclude the upper value. If the ending time should be included use e.g. tsrange(start_date, end_date, '[]')

Alternatively you can use the standard compliant overlaps operator:

select *
from the_table
where (start_date, end_date) overlaps (timestamp '2022-11-07 08:00:00', timestamp '2022-11-07 09:00:00');

I am however not 100% that both solutions behave the same, especially with edge cases.