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

Question

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.

Have you created the corresponding [index](https://stackoverflow.com/questions/50305328/firestore-whereequalto-orderby-and-limit1-not-working)? — Alex Mamo, Feb 15 '22 at 09:10
Your `DateTime` creation seems to be fine and as I've tried it, the `where()` function should be able to take care of comparing two instances of `DateTime` without a problem. What I can suggest is that you do divide and conquer and put one `where()` clause in your chain at a time and test the results. What could also be a possible issue is timezones. Are you sure the time-zones are correct and in-line with each other? — Vandad Nahavandipoor, Feb 15 '22 at 09:40
@AlexMamo, hmm, it's weird that the logcat is not showing me the error message includes a direct link to create the missing index in the Firebase console. It usually does since I'm using android studio. Let me check again. — scott lee, Feb 15 '22 at 15:15
@VandadNahavandipoor, yup the time zones are correct and in line with each other. Yes using only one where() clause at one time allows the query to be done correctly. It's just when I combined the query that it fails to work. — scott lee, Feb 15 '22 at 15:17

Renaud Tarnec · Accepted Answer · 2022-02-15T10:33:33.343

3

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

edited Feb 15 '22 at 10:33

answered Feb 15 '22 at 10:27

Renaud Tarnec

79,263
10
95
121

1

Ahh, I see. Executing two queries and merging the results seems like a viable solution. Thanks for the suggestion. – scott lee Feb 15 '22 at 15:19
The other workaround may not work for my case since the gap between my 2 date range can be rather big with many data in between. – scott lee Feb 15 '22 at 15:19

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

1 Answers1