Flutter Cloud Firestore orderBy and limit issues

Question

I have a collection in cloud firestore that contains a lot of documents that have a timestamp value for when the document was created, along with some more information. I'm experiencing some very strange behavior, that I just can't wrap my head around.

What I'm trying to do is:

• Find the newest document based on timestamp value
• Find the oldest document that is newer than 1 hour ago, 24 hours ago, and 7 days ago.

I have these queries:

var snapshotNow = await Firestore.instance
    .collection(stationName)
    .orderBy('servertime', descending: true)
    .limit(1)
    .snapshots()
    .first;

And to find 1 hour ago, and so on:

var dateTime = DateTime.now().subtract(duration);

And to retrieve the document from 1 hour ago:

var snapshotThen = await Firestore.instance
    .collection(stationName)
    .where('servertime', isGreaterThan: dateTime)
    .orderBy('servertime', descending: false)
    .limit(1)
    .snapshots()
    .first;

For some reason, these two very different queries retrieve the same document each time. That is: I get the newest document in the collection from both queries.

I tried to do a couple of things to find out what the problem is:

1) Not looking at the date, by removing the .where in the query. This should make snapshotThen become the oldest document in the collection. What I actually received was the newest document in the collection. This means that the same document was returned as the first document when sorting both ascending and descending.

2) Increasing the limit to more than 1 document. When increasing the limit from limit(1) to limit(10). For the ascending-sort, this consistently returns the newest 10 documents (as expected). For the orderBy descending, something weird happens. I expect it return the oldest 10 documents from the collection. What it returns is one of two things; either 1) the 10 newest documents, sorted from oldest to newest, or 2) only the newest document (not a list of 10 documents, but a list of only 1 document).

Any help is greatly appreciated, I've been working on this for ages, and I just can't wrap my head around where the problem lies. What I find the most frustrating is that the behavior changes; sometimes I get only 1 document, sometimes I get 10 documents, all with the same query.

Answer 1

From my point of view the issue lies in the format of the Firestore timestamp.

An example Firestore timestamp is as follows:

Timestamp { _seconds: 1575888466, _nanoseconds: 725000000 }

I have replicated what you have described and it works well using the Firestore.Timestamp

You can use Firestore.Timestamp.now() to get the current the current time in the Firestore timestamp format and Firestore.Timestamp.fromMillis(Date.now()-[MS]), where [MS] is the time you want to subtract in milliseconds.

1 hour = 3600000 ms

1 day = 86400000 ms

So an example would be the following: (I'm using Node.js)

let postsRef = db.collection('dates');

const hour= 3600000;
const fullday= 86400000;

var firenow = Firestore.Timestamp.now()
var firepast = Firestore.Timestamp.fromMillis(Date.now()-fullday);

var range1 = postsRef.where("time", ">=", firepast).where("time", "<=", firenow);
let query1 = range1.get()
  .then(snapshot => {
    if (snapshot.empty) {
      console.log('No matching documents.');
      return;
    }
    snapshot.forEach(doc => {
      console.log(doc.id, '=>', doc.data());
    });
  })
  .catch(err => {
    console.log('Error getting documents', err);
  });

The above will give you the Documents with timestamp in the last hour, replacing hour with fullday would give you the last 24h.