query on date field in firestore not working

Question

I'm new to programming and only as a hobby, usually if I have a problem I can find the answer on the internet somewhere, but having spent best part of 2 days and still can't figure this one out. I'm programming an android app using kotlin and android studio. First question asked, please be gentle!

I have a date saved in Firestore as timestamp saved with this code through hashmap

    val dateFormat = SimpleDateFormat("dd-MM-yyyy")
    val date = findViewById<TextView>(R.id.textMatchDate).text.toString()
    val timeStamp = Timestamp(dateFormat.parse(date)

timeStamp is then saved to firestore.

My code for retrieval is

        val date = "01-01-2020"
        val date1 = dateFormat.parse(date)
        val startDate = Timestamp(date1)

val db = FirebaseFirestore.getInstance()
        db.collection("snookerResults")
            .whereGreaterThan("date", startDate)
            .whereEqualTo("homeTeam", team)
            .get()
            .addOnSuccessListener {...}

I've put the means 0f getting startDate for simplicity it is is derived programatically but is string of the same format. I get an empty array despite knowing ther are results that should be returned. If I comment out the whereGreaterThan line I get all results returned.

I have Log outputs to illustrate the problem when I run normally I get this output:-

2020-08-29 23:27:04.858 17112-17112/com.example.aggregatecalculator I/handicap: chosen team is The Brunswick

2020-08-29 23:27:05.086 17112-17112/com.example.aggregatecalculator I/handicap: startdate is Timestamp(seconds=1577836800, nanoseconds=0)

If I comment out the whereGreaterThan line then I get this output

2020-08-29 23:37:08.331 18164-18164/com.example.aggregatecalculator I/handicap: chosen team is The Brunswick

2020-08-29 23:37:08.582 18164-18164/com.example.aggregatecalculator I/handicap: startdate is Timestamp(seconds=1577836800, nanoseconds=0)

2020-08-29 23:37:08.583 18164-18164/com.example.aggregatecalculator I/handicap: date is [Wed Jan 01 00:00:00 GMT+00:00 2020, Wed Sep 30 00:00:00 GMT+01:00 2020]

2020-08-29 23:37:08.793 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1583712000, nanoseconds=0)

2020-08-29 23:37:08.793 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1575244800, nanoseconds=0)

2020-08-29 23:37:08.793 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1569798000, nanoseconds=0)

2020-08-29 23:37:08.794 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1572825600, nanoseconds=0)

2020-08-29 23:37:08.794 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1582502400, nanoseconds=0)

2020-08-29 23:37:08.794 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1584921600, nanoseconds=0)

2020-08-29 23:37:08.795 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1568588400, nanoseconds=0)

2020-08-29 23:37:08.795 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1574035200, nanoseconds=0)

2020-08-29 23:37:08.795 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1571612400, nanoseconds=0)

2020-08-29 23:37:08.796 18164-18164/com.example.aggregatecalculator I/handicapresults: date is Timestamp(seconds=1571007600, nanoseconds=0)

As you can see timestamp is same format and 3 returned timestamp's are greater than the startdate so in theary should return with the whereGreaterThan clause

fun getNewHcap(team: String){
val players = arrayListOf<String>()
val db = FirebaseFirestore.getInstance()
db.collection("SnookerPlayers")
    .whereEqualTo("league", "Big Table Monday")
    .whereEqualTo("team", team)
    .get()
    .addOnSuccessListener { task ->
        for (doclist in task){
            val player = doclist["player"].toString()
            players.add(player)
        }
        val dates = hcapMinMaxDate()
        val dateFormat = SimpleDateFormat("dd-MM-yyyy")
        val date = "01-01-2020"
        val date1 = dateFormat.parse(date)
        val startDate = Timestamp(date1)
        val endDate = Timestamp(dates[1])
        Log.i("handicap", "startdate is $startDate")
       // Log.i("handicap", "startdate is $endDate")

        val db = FirebaseFirestore.getInstance()
        db.collection("snookerResults")
            //.whereGreaterThan("date", startDate)
            .whereEqualTo("homeTeam", team)

           //.whereLessThan("date", endDate)
            .get()
            .addOnSuccessListener { task ->
                for (docList in task){
                   // val player = docList["home player 1"].toString()
                    val date = docList["date"].toString()

                    Log.i("handicapresults", "date is $date")
                }
            }


        Log.i("handicap", "date is $dates")

    }
Log.i("handicap", "chosen team is $team")

Answer 1

I get an empty array despite knowing there are results that should be returned. If I comment out the whereGreaterThan() line I get all results returned.

You are getting an empty array most likely because of the following line of code:

db.collection("snookerResults")
    .whereGreaterThan("date", startDate)
    .whereEqualTo("homeTeam", team)
    .get()
    .addOnSuccessListener {...}

And this is because calling .whereGreaterThan() along with .whereEqualTo() in the same query, require an index. This index can be created manually in your Firebase Console or if you are using Android Studio, you'll find in the logcat a message that sounds like this:

FAILED_PRECONDITION: The query requires an index. You can create it here: ...

You can simply click on the link or copy and paste the URL into a web browser and your index will be created automatically. Wait a few minutes until the index is created and you'll get the desired results.

However, this approach might not be the best one, since you need to create an index for each homeTeam in your database. It will work if you have a few or a fixed number of teams but it will be hard if you have lots of them, case in which it's recommended to duplicate the data. This practice is called denormalization and it's quite common practice when it comes to Cloud Firestore. If you are new to the NoSQL database, for a better understanding, I recommend you see this video, Denormalization is normal with the Firebase Database. It's for Firebase Realtime Database but the same principles apply to Cloud Firestore. Besides that, I think you might also be interested in my answer from the following post:

What is denormalization in Firebase Cloud Firestore?

So in your particular case, I'd create a new collection that looks like this:

Firestore-root
  |
  --- homeSnookerResults (collection)
         |
         --- homeTeamId (document)
               |
               --- allResults (collection)
                     |
                     --- resultIdOne (document)
                           |
                           --- //result object properties

In this case, a query that looks like this, will do the trick:

db.collection("homeSnookerResults")
    .document(homeTeamId)
    .collection("allResults")
    .whereGreaterThan("date", startDate)
    .get()
    .addOnSuccessListener {...}

Case in which, no index is required.