Self HostedDatabases

Datetime query with "less_than" doesn't return expected values

Oct 20, 2024, 17:00

View on Discord

Votes

Replies

Participants

Unknown

Messages

weasel82Original post

Web Developer

Oct 20, 2024, 16:17

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:

Plain text

        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:

Plain text

        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

Summary

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!

weasel82

Web Developer

Oct 20, 2024, 16:30

Some more troubleshooting:

Plain text

        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...

Datetime query with "less_than" doesn't return expected values

Need support?_