Back
Datetime query with "less_than" doesn't return expected values
I have a collection containing documents with an attribute startDate, which is ranging from the past into the future. I try to query as follows:
TypeScript
database_id='',
collection_id='',
queries=[Query.greater_than_equal('startDate', datetime.datetime.now(datetime.timezone.utc).replace(microsecond=0).isoformat())]
)
for document in duties['documents']:
print(document['startDate'])```
The return is as expected for today being the 20th of Oct 2024:
```2024-10-28T02:45:00.000+00:00
2024-10-29T02:00:00.000+00:00
2024-10-30T02:00:00.000+00:00
2024-10-31T02:00:00.000+00:00
2024-11-01T02:00:00.000+00:00
But if I try the same with less_than_equal, I get unexpected behavior:
TypeScript
database_id='',
collection_id='',
queries=[Query.less_than_equal('startDate', datetime.datetime.now(datetime.timezone.utc).replace(microsecond=0).isoformat())]
)
for document in duties['documents']:
print(document['startDate'])```
returns:
```2024-09-17T11:30:00.000+00:00
2024-09-18T03:00:00.000+00:00
2024-09-18T14:05:00.000+00:00
2024-09-18T17:01:00.000+00:00
2024-09-19T09:05:00.000+00:00
2024-09-19T11:22:00.000+00:00```
... which isn't wrong. But its incomplete, as several documents with start date between the 20th of September and today are missing.
What am I doing wrong?
Thanks, cheers
Roland
TL;DR
Datetime queries with "greater_than_equal" show expected results, but issues arise with using "less_than_equal" as some expected documents are missing between the targeted date range.
The provided Python code creates datetime queries that seem to get stuck at the 20th of September or October instead of returning the expected values. When using "less_than_equal" in the query, it doesn't return all documents within the targeted date range.
A possible solution could involve examining the data entries closely to see if there are any anomalies, checking the query syntax for accuracy, and ensuring that the datetime values are properly formatted in the database.
Cheers! Some more troubleshooting:
TypeScript
database_id='',
collection_id='',
queries=[Query.between('startDate','2024-09-01','2024-12-31')]
)
for document in duties['documents']:
print(document['startDate'])```
Output:
```2024-09-15T08:55:00.000+00:00
2024-09-15T11:02:00.000+00:00
2024-09-16T04:12:00.000+00:00
2024-09-16T07:31:00.000+00:00
2024-09-16T13:07:00.000+00:00
2024-09-17T05:44:00.000+00:00
2024-09-17T08:33:00.000+00:00
2024-09-17T11:30:00.000+00:00
2024-09-18T03:00:00.000+00:00
2024-09-18T14:05:00.000+00:00
2024-09-18T17:01:00.000+00:00
2024-09-19T09:05:00.000+00:00
2024-09-19T11:22:00.000+00:00
2024-09-19T13:29:00.000+00:00```
But:
```duties = db.list_documents(
database_id='',
collection_id='',
queries=[Query.between('startDate','2024-10-01','2024-12-31')]
)
for document in duties['documents']:
print(document['startDate'])```
Output: `
```2024-10-15T07:36:00.000+00:00
2024-10-15T11:05:00.000+00:00
2024-10-16T04:14:00.000+00:00
2024-10-16T06:33:00.000+00:00
2024-10-16T08:47:00.000+00:00
2024-10-18T05:35:00.000+00:00
2024-10-18T07:30:00.000+00:00
2024-10-18T10:11:00.000+00:00
2024-10-19T05:25:00.000+00:00```
And:
```duties = db.list_documents(
database_id='',
collection_id='',
queries=[Query.between('startDate','2024-10-21','2024-12-31')]
)
for document in duties['documents']:
print(document['startDate'])```
Returns:
```2024-10-21T09:15:00.000+00:00
2024-10-21T10:50:00.000+00:00
2024-10-21T12:50:00.000+00:00
2024-10-24T06:00:00.000+00:00
2024-10-25T06:00:00.000+00:00
2024-10-28T02:45:00.000+00:00
2024-10-29T02:00:00.000+00:00
2024-10-30T02:00:00.000+00:00
2024-10-31T02:00:00.000+00:00
2024-11-01T02:00:00.000+00:00
2024-11-03T23:00:00.000+00:00
2024-11-05T05:55:00.000+00:00
2024-11-05T08:55:00.000+00:00```
So somehow all queries seem to be stuck either at the 20th of September or the 20th of October...
