I am trying to return all rows where the time range overlaps of a query. Below you can see my code and an example row that does not get returned by the query but is in the database.
I have used following SO post as a guideline: Determine Whether Two Date Ranges Overlap
a_projects = and_(Project.start <= datetime.fromisoformat(data["end"]), datetime.fromisoformat(data["start"]) <= Project.end)
b_projects = and_(datetime.fromisoformat(data["start"]) <= Project.end, Project.start <= datetime.fromisoformat(data["end"]))
clause_args_projects = [a_projects, b_projects]
or_clauses_projects = or_(*clause_args_projects)
project_query = session.query(Project).filter(or_clauses_projects)
df_projects = pd.DataFrame(query_to_dict(project_query))
print("DF Projects: \n", df_projects)
The query:
'start': '2022-02-01', 'end': '2022-04-30'
The above code returns the following results:
i name start end total acquired
0 1 Testproject 2022-04-04 2022-05-28 2000000 True
1 2 Prime 2022-03-14 2022-05-06 100000 False
2 3 University 2022-03-21 2022-04-09 50000 True
3 4 Stackoverflow 2022-03-28 2022-04-15 200000 True
The returned results seem great as expected. I validated the result with the database and not all true results are returned. For example, I have found the following row:
i name start end total acquired
15, Missing Project, 2022-03-28, 2022-04-22, 400000, true
Why is the "Missing Project" not returned? What have I missed?
