Firestore query by date range

Question

I need the help to query long collection with date range. See the below example document. I wanna query startTime field using date range.

Answer 1

Since I have the dueDate field stored as "timestamp" (and NOT as string or number) on Cloud Firestore, I did this to get the invoice documents with a due date on 2017:

let start = new Date('2017-01-01');
let end = new Date('2018-01-01');

this.afs.collection('invoices', ref => ref
  .where('dueDate', '>', start)
  .where('dueDate', '<', end)
);

NOTE: dueDate field was stored at firebase with a Date() object. e.g.: this.doc.dueDate = new Date('2017-12-25')

Answer 2

You could store the datetime object as Unix time (seconds since 1 January 1970). Then you can simple use the where select like this:

collectionRef.where("startTime", ">=", "1506816000").where("startTime", "<=", "1507593600")

Btw - to convert from datetime to Unix time in your app, you can use the excellent (now deprecated) library moment (if you are building something with js or node).

Answer 3

    var startfulldate = admin.firestore.Timestamp.fromDate(new Date(1556062581000));
    db.collection('mycollection')
      .where('start_time', '<=', startfulldate)
      .get()
      .then(snapshot => {              
            var jsonvalue: any[] = [];
            snapshot.forEach(docs => {
              jsonvalue.push(docs.data())
               })
                 res.send(jsonvalue);
                 return;
                }).catch( error => {
                    res.status(500).send(error)
                });

Answer 4

const event = new Date();
const expirationDate = admin.firestore.Timestamp.fromDate(event);
const query = collectionRef.where('startTime', '<=', expirationDate)

Answer 5

As startTime stored as Timestamp, you can do this query range for more accururate (this good for both condition of long date range or same date range).

const start = new Date('2021-01-01T00:00:00.000z');
const end = new Date('2021-03-01T23:59:59.000z');

db.collection('Data').where('startTime', '>=', start).where('startTime', '<=', end).get().then(data => {
   //pass your 'data' here
});

I used this in my Node.js apps. Hopefully this useful.

Answer 6

For everyone recently using Firebase Firestore, there's a difference depending on your settings of your Firebase implementation (depending on the firebase version).

Before, Firestore was saving Timestamp as a Date, however as described here in the docs the will be replaced soon by a Timestamp object. See the Timestamp docs here.

You can force your implementation already by adding a setting in your code to force Firebase to use Timestamp objects instead of Date like this example:

var firebaseApp = firebase.initializeApp({
    apiKey: [APIKEY],
    authDomain: [FIREBASEAPPDOMAIN],
    projectId: [PROJECTID]
});

var firestore = firebase.firestore();
var settings = { timestampsInSnapshots: true }; // force Timestamp instead of Date
firestore.settings(settings);

Answer 7

The solution is to use Date.now(). Stop using timestamp service from Firebase, you need to work with the numerical value of the time in milliseconds like for example: 1514271367000, instead if Firestore uses 26/12/2017 1:56:07 GMT- 0500 (-05) will not work. An example of a query is:

this.fsService.afs.collection('chats/4bY1ZpOr1TPq8bFQ3bjS/finance/123+finance/12345'
          , ref => ref.orderBy('hour').startAt(1514184967000).endAt(1514271367000))
          .valueChanges().subscribe(data =>{
            this.mensajes = data;
          })

Answer 8

Those who, like me, are using PHP to access Firestore, can do something like this:

$startTime = new DateTime('2020-05-23 00:00:00');
$endTime = new DateTime('2020-06-23 23:59:59');

$start = new Google\Cloud\Core\Timestamp($startTime);
$end = new Google\Cloud\Core\Timestamp($endTime);

// fb is a Google\Cloud\Firestore\FirestoreClient object
$this->query = $this->fb->collection('your_collection');

$aux = $this->query;
$aux = $aux->where('startTime', '<', $end);
$aux = $aux->where('startTime', '>', $start);

return $aux->documents();

Enjoy.

Answer 9

Generic function to find documents in a collection by date range of specifics fields:

public List<QueryDocumentSnapshot> findDocsByDateRange(
                                          String collection, 
                                          String fieldStartDate,
                                          String fieldEndDate,
                                          Date startDate, 
                                          Date endDate) {
    ApiFuture<QuerySnapshot> querySnapshot = fireStore()
        .collection(collection)
            .whereGreaterThanOrEqualTo(FieldPath.of(fieldStartDate), startDate)
                .whereLessThanOrEqualTo(FieldPath.of(fieldEndDate), endDate)
                    .get();
    return querySnapshot.get().getDocuments();
}

Packages:

import com.google.api.core.ApiFuture;
import com.google.cloud.firestore.DocumentSnapshot;
import com.google.cloud.firestore.FieldPath;
import com.google.cloud.firestore.Firestore;
import com.google.cloud.firestore.QueryDocumentSnapshot;
import com.google.cloud.firestore.QuerySnapshot;

Answer 10

In a frontend application, this is how Firebase timestamps and dates can be used to query and store documents.

Answer 11

I think this will help you out,

yourMethod() {
    var date = DateTime.now();//
    print("First Date > " + DateTime(date.year, date.month, 1).toString());
    var datex = new DateTime(date.year, date.month + 1, 0);
    print("Last Date > " +datex);//
    //
    Firestore.instance
        .collection('biling')
        .where("driverId", isEqualTo: widget.uid)
        .where("date",
            isGreaterThanOrEqualTo:
                new DateTime(date.year, date.month, 1).toString())//1
        .where("date", isLessThanOrEqualTo: datex.toString())//2
        .orderBy('date', descending: true)
        .getDocuments()
        .then(
          (QuerySnapshot snapshot) => {
            snapshot.documents.forEach((f) {
              if (this.mounted) {
                setState(() {
                  totalP += double.tryParse(f.data["price"]);
                });
              }
              print("_price " + f.data["price"]);
              print("_duePaymntForCompay " + f.data["duePaymntForCompay"]);
            }),
          },
        );
  }

Answer 12

now you need to use these queries for filtering documents with conditions because .where() is not working for me

  db.collection("id").whereGreaterThan("field","value")
  .whereEqualTo("field","value")
  .whereLessThen("field","value")

Answer 13

What worked for me was Format Date with Moment JS and split into Day, Month & Year

const currentDate = moment().format("DD-MM-YYYY").split("-");
const currentDay = currentDate[0];
const currentMonth = currentDate[1];
const currentYear = currentDate[2];


const allDocuments = await collectionRef
.doc(docId)
.collection(*COLLECTION NAME*)
.where(
  *DATE PARAMETER NAME*,
  ">=",
  new Date(`${currentYear}-${currentMonth}-${currentDay}`)
)
.where(
  *DATE PARAMETER NAME*,
  "<",
// ${parseInt(currentDay) + *Number of days you want in range*} 
  new Date(`${currentYear}-${currentMonth}-${parseInt(currentDay) + 1}`)
)
.get();