I'm working with a software that use a database with a given model. I can't change the model.

It's a table with item owned by a person during a specified period.

Here is how it works :

In this software when I give a first item to an owner, it wrotes the date_from to 2020-01-01 by default and the date_to to 2099-12-31.

So basically when Date_To value is 2099-12-31 it means that this item is the one that is currently owned by Louis.

So If I have a value like this :

It mean that the item Game is the first item owned by John (because of the 2000-01-01) and it is still owned by him (because of the 2099-12-31).

Louis can only own one item for a period. He has to bring the item back to get an new one. When he does this, the software change the current Date_to with the return date. And the new item has a Date_From that is one day after the return date and a Date_To to 2099-12-31.

Like this (the return date is 2021-03-31) :

But now what I'm trying to do is the know the items that were owned by an owner for a given period.

Here is an example :

For the first table, the owner Louis I wanna know the items owned by him from the 2021-01-01 to the 2021-12-31.

I can simply do this :

where date_from >= '2021-01-01' and date_to <= '2021-12-31'

But here is the problem... It will only give me the second row (the hat owned by Louis) since the software use default date format for current and first item. But in that case, Louis has 3 items owned during the year 2021.

So my question is :

How can I deal with my 2 default values ? Since those values are always the same. I wanna do something like : if the date_from value is between my where condition or equal to 2000-01-01 and Date_to is between the condition or equal to 2099-12-31 then display the item.

CodePudding user response：

You're basically trying to test for periods of ownership that overlap with the periods of your query, but your current test doesn't do this. It's close though.

Two periods overlap if the first starts before the second ends and the second starts before the first ends¹.

Which is as simple as:

where date_to >= '2021-01-01' and date_from <= '2021-12-31'

I.e. just swap which columns you're comparing to the start and end values of your period.

And no need to special case the default start and end marker dates unless those are fixed and you need to support queries for other centuries as well.

¹Your current test was "does one period entirely contain another period". I've seen people tie themselves in knots trying to enumerate different types of overlaps and write unique tests for each case but it turns out that there's a simpler test that doesn't need to enumerate those cases, and that's what I show here. The only slight complexity is picking the right values of < or <= when you've decided whether two periods that exactly abut one another should or should not be considered an overlap.

CodePudding user response：

Well, you've already said the answer

"date_from value is between my where condition or equal to 2000-01-01 and Date_to is between the condition or equal to 2099-12-31"

 ...
 where (date_from >= '2021-01-01' or date_from = '2000-01-01')
   and (date_to <= '2021-12-31' or date_to = '2099-12-31')

This should help