Skip to content
Start project
Back

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

  • 0
  • Self Hosted
  • Databases
View on Discord
weasel82
20 Oct, 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:

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!
weasel82
20 Oct, 2024, 16:30

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

Reply to this thread by joining our Discord

Reply on Discord

Recommended threads

Need support?

Join our Discord

Get community support by joining our Discord server.

Join Discord

Get premium support

Join Appwrite Pro and get email support from our team.

Learn more