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

user_1835175 0
Viewed 427

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 Imgur

period.startsAt and period.endsAt are Dart Datetime

google-cloud-firestore firebase dart flutter google-cloud-platform
1 Answers

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.

user_52468850
Related