How to exclude a datetime range in firestore query in flutter?-CodePudding

I am trying to query for data from 2022-01-01 to 2022-01-18 and data after 2022-01-26. Data from 2022-01-18 to 2022-01-26 is to be excluded.

However, when I run the query below, no data is returned.

DateTime dateTime2 = 2022-01-01 21:53:50.793
DateTime dateTime2 = 2022-01-26 21:53:50.793
DateTime dateTime3 = 2022-01-18 21:53:50.793

firestore.collection('posts')
             .limit(9)
              .where('timeStamp', isGreaterThan: dateTime1)
              .where('timeStamp', isGreaterThan: dateTime2)
              .where('timeStamp', isLessThan: dateTime3)
              .orderBy('timeStamp', descending: true)
              .get()

If I were to take out

.where('timeStamp', isGreaterThan: dateTime1)
.where('timeStamp', isGreaterThan: dateTime2)

the query works just fine. Can anyone advise what is wrong with my query or how can I get data that excludes data between two dates? Thank you.

CodePudding user response：

You cannot query a Firestore collection with two different ranges of values like (pseudo-code)

timeStamp between 2022-01-01 and 2022-01-18 OR timeStamp between 2022-01-26 and now

This is a consequence of the way Firestore indexes the data. You'll find some very clear explanations in this official video.

So you need to execute two queries and merge their results in your front-end.

Or, if the gap between the two date ranges is not very big, you could possibly query for timeStamp between 2022-01-21 and now and, in the front-end, remove the undesired dates in the middle. That's clearly a workaround which may not make sense since you limit your query with .limit(9).