How to retrieve Firebase Firestore documents between 2 dates stored as timestamps with FlutterFire plugins

Question

I'd like to retrieve all orders paid between two dates:

    var snapshot = await db
        .collection('order')
        .orderBy('fullyPaidAt')
        .where('fullyPaidAt',
            isGreaterThan: Timestamp.fromDate(period.startsAt))
        .where('fullyPaidAt', isLessThan: Timestamp.fromDate(period.endsAt))
        .limit(10)
        .get();

However, I am getting results only if I use one of the where clauses. But I receive no results if I combine the 2 clauses. Is it one limitation of Firebase Firestore?

I am storing the dates in the order collection as timestamps.

See the screenshot below

period.startsAt and period.endsAt are Dart Datetime

Solution

Edit:

The problem wasn't the lack of index, but the fact that were no documents in between the period.startsAt and period.endsAt. After adding the documents with the correct date, the problem disappeared.

---

> I am storing the dates in the order collection as timestamps.

If you are storing the dates as Timestamp objects, then:

I am getting results only if I use one of the where clauses. But I receive no results if I combine the 2 clauses.

Provides the expected behavior since such a query requires an index. There's no way you can perform such a query without it.

Is it one limitation of Firebase Firestore?

No, that's a feature that can help you perform very fast queries. That being said, you can create the required index manually directly in the Firebase Console or you'll find in your IDE a message that sounds like this:

FAILED_PRECONDITION: The query requires an index. You can create it here: ...

You can simply click on that link or you can copy the URL and paste it into a web browser and your index will be created automatically for you.

Comments

[ckyony]

I thought I don't need to create the index since single indexes are created automatically for each field. Therefore, because I am using only one single field ('fullyPaidAt'), I don't need to create manually an index. Am I correct? Let me also clarify that I am using the timestamp type for the fullyPaid field.

[Alex Mamo]

Oh yes, you're right. I thought I've seen the orderBy call on a different field. In this case, please edit your question and add your database structure as a screenshot and show what does period.startsAt and period.endsAt return.

[ckyony]

Thank you for taking the time to respond.

[Alex Mamo]

Then are you sure that you have documents with a date that lies in between?

[ckyony]

You're right. To my dismay and shame, there is no document for that period. I upload the missing documents. The query was properly. Sorry for wasting your time.

[Alex Mamo]

I just updated my answer.