Querying data in firestore by timestamp

Question

I'm querying documents by timestamp and it is returning an empty array. However it works when I use "==" ex:.where('date', '==',timestamp), and returns empty array when I use '>=' or '<='.

I have tried to convert timestamp into date object and string also but no success.

Note: The date field in firestore is of type Timestamp.

I'm querying documents with date greater than '2018-08-03' in the collection.

Below is a picture of the collection of transactions (left) and the document(right) which should be part of the array of documents returned, because the date is greater than '2018-08-03'

Below is my code.

  const firstDay = new Date('2018-08-03');
  const timestamp1 = admin.firestore.Timestamp.fromDate(firstDay);
  const trans = [];
  const docRef = db.collection('Users').doc(uid).collection('transactions').where('item_id', '==', item_id)
    .where('date', '>=', timestamp1);
  await docRef.get()
    .then((snapshot) => {
      snapshot.forEach((doc) => {
        trans.push({ transaction_id: doc.id, transaction: doc.data() });
      });
    })
    .catch(err => new Error('cannot get the documents', err));

Expected result: should be an array with transactions with date greater than specified above.

Actual result: empty array.

Since its working for equality ==, I assumed that >= and <= would work. Anything I'm missing here?

Logs after trying ">" (equal to timestamp1)

Firestore (2.3.0) 2019-10-03T14:01:25.013Z AObRG [ClientPool.acquire]: Re-using existing client with 100 remaining operations
Firestore (2.3.0) 2019-10-03T14:01:25.015Z AObRG [Firestore.readStream]: Sending request: {"parent":"projects/valuemo-000/databases/(default)/documents/Users/Xmr3vKT19OSST02DSTDMt0jSq692","structuredQuery":{"from":[{"collectionId":"transactions"}],"where":{"compositeFilter":{"op":"AND","filters":[{"fieldFilter":{"field":{"fieldPath":"item_id"},"op":"EQUAL","value":{"stringValue":"zGRkpP3QkzI89zKyDjZ7FPDrXd5G3Bco5ENlR"}}},{"fieldFilter":{"field":{"fieldPath":"date"},"op":"EQUAL","value":{"timestampValue":{"seconds":1533254400}}}}]}}}}
Firestore (2.3.0) 2019-10-03T14:01:25.232Z AObRG [Firestore._initializeStream]: Received stream error: { Error: The query requires a COLLECTION_ASC index 
for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB
    at Http2CallStream.call.on (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call.js:68:41)
    at Http2CallStream.emit (events.js:194:15)
    at process.nextTick (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call-stream.js:71:22)
    at process.internalTickCallback (internal/process/next_tick.js:70:11)
  code: 9,
  details:
   'The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB',
  metadata: Metadata { options: undefined, internalRepr: Map {} } }
Firestore (2.3.0) 2019-10-03T14:01:25.256Z AObRG [Firestore._initializeStream]: Received initial error: { Error: The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB
    at Http2CallStream.call.on (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call.js:68:41)
    at Http2CallStream.emit (events.js:194:15)
    at process.nextTick (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call-stream.js:71:22)
    at process.internalTickCallback (internal/process/next_tick.js:70:11)
  code: 9,
  details:
   'The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB',
  metadata: Metadata { options: undefined, internalRepr: Map {} } }
Firestore (2.3.0) 2019-10-03T14:01:25.260Z AObRG [Firestore._retry]: Request failed with unrecoverable error: { Error: The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB
    at Http2CallStream.call.on (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call.js:68:41)
    at Http2CallStream.emit (events.js:194:15)
    at process.nextTick (D:\Projects\Valuemo-firebase\node_modules\@grpc\grpc-js\build\src\call-stream.js:71:22)
    at process.internalTickCallback (internal/process/next_tick.js:70:11)
  code: 9,
  details:
   'The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet. See its status here: https://console.firebase.google.com/project/valuemo-000/database/firestore/indexes/single_field?create_exemption=ClJwcm9qZWN0cy92YWx1ZW1vLTAwMC9kYXRhYmFzZXMvKGRlZmF1bHQpL2NvbGxlY3Rpb25Hcm91cHMvdHJhbnNhY3Rpb25zL2ZpZWxkcy9kYXRlEAEaCAoEZGF0ZRAB',
  metadata: Metadata { options: undefined, internalRepr: Map {} } }

Please edit the question to show exactly the documents in the collection that you expect to be returned by this query, along with the actual values for `firstDay` and all other variables. Without seeing actual data, we have no idea if you're asking for documents that match the query. Your question should illustrate that the query definitely does not match your expectation, for everyone to see. — Doug Stevenson, Sep 25 '19 at 20:36
@DougStevenson I added a picture of the database structure and a document with its field. — J.D, Sep 25 '19 at 20:50
I think you might be comparing wrong types. you are comparing date with a timestamp. A timestamp value is getting compared with a date type. Here is some more info: https://stackoverflow.com/questions/19141030/difference-between-oracle-date-and-timestamp — Aye_baybae, Sep 25 '19 at 20:58
@Aye_baybae dates are comparable to timestamps. That's not a problem. The issue that still is unclear is the timezone of the date. Which timezone is required for `2018-08-03`? Not everyone in the world is on the same day. — Doug Stevenson, Sep 25 '19 at 21:03
@DougStevenson I'm ignoring the timezone, the thing that I don't understand i why using '==' is returning the document with that specific timestamp but using '>=' or '<=' is not working. — J.D, Sep 25 '19 at 21:15
I just tested it with a new project and the behavior of the ‘==’ and the ‘>=’ operators worked as expected for a timestamp field. I believe @DougStevenson is correct in this case. It would help a lot if you could share a log of the values from the query that does work, and also show us how you are declaring the value for the `firstDay` variable. — pessolato, Sep 30 '19 at 12:40
@pessolato adde the logs and the firstDay variable line at the code section. — J.D, Oct 01 '19 at 10:44
I apologise, what I meant in my previous comment was to log the results from the query with the `==` operator, the logs for the actual query object do not provide any useful information. — pessolato, Oct 02 '19 at 13:57

score 4 · Accepted Answer · answered Oct 03 '19 at 13:51

So I was testing it out, with what I believe to be a very similar set up to what you presented, and I was actually able to reproduce the same behaviour.

After testing different possibilities, I found that the issue for me had nothing to do with the datatype of the fields, but with the configuration of the composite index.

The composite index configuration required to make this query work was the following:

According to the Queries supported by composite indexes section of the documentation, the indexes for these kind of compound queries should have the equality filtered field first, this is not explicitly said, but it is how the docs present it in the examples.

Let me know if this managed to resolve the issue for you.

score 0 · Answer 2 · answered Sep 26 '19 at 05:04

0

Take your date and convert into javascript date object. Then do getTime() on converted date object it will return a number. Then get firestore timestamp and do getSeconds().Compare seconds value of firestore timestamp with the variable storing output of getTime().

answered Sep 26 '19 at 05:04

Karan Hotwani

24
4

Yeah, but how am I supposed to use it on the where clause? – J.D Sep 26 '19 at 10:05
get all the data and store it in some data structure like array or object. Then traverse it and compare each of them and store valid data in another array &then use it. There is no way to do the comparison of two different types of date directly using where clause. Also u don't have to use then after using async await. Just write the code directly after await – Karan Hotwani Sep 26 '19 at 11:20

score 0 · Answer 3 · answered Nov 16 '20 at 04:40

0

The query requires a COLLECTION_ASC index for collection transactions and field date. That index is not ready yet.

Why not create the index as the error is suggesting? This might fundamentally solve your issue.

answered Nov 16 '20 at 04:40

abbasaleid

1

Querying data in firestore by timestamp

3 Answers3