Firestore query by date range

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

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')

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).

    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)
                });

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

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);

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.

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;
          })

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.

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;

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"]);
            }),
          },
        );
  }

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

https://i.stack.imgur.com/MiBqy.png" alt="Firestore date usage">

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();