How to get firebase record between two particular dates?

Question

I want to get the medical record of the patient from the Firebase Realtime Firebase DB between the "CurrentDate" up to 2 months "PreviousDate". The problem I'm facing is that the dates are getting into a query that I will check via debugging code. Here is the code through which I'll get "CurrentDate" and "PreviousDate":

    date = Calendar.getInstance().time;
    val dateFormat = SimpleDateFormat("d-M-yyyy")
    currentDate = dateFormat.format(date)
    cDate.text = currentDate //this gives me **24-4-2021**


    val calendar = Calendar.getInstance()
    calendar.add(Calendar.MONTH, -2)
    val preDate = calendar.time
    previousDate = dateFormat.format(preDate)
    pDate.text = previousDate /// this gives me **24-2-2021**

Here is the code of the following function which I write to get the record:

private fun browseAppointmentByDate() {
    mFirebaseInstance = FirebaseDatabase.getInstance()
    appointmentDBReference = mFirebaseInstance!!.reference
    completeAppointArrayList = ArrayList()

    val ref = FirebaseDatabase.getInstance()
    ref.getReference("PatientChecked").child(currentUserID).orderByChild("date").startAt(previousDate)
            .endAt(currentDate)
            .addListenerForSingleValueEvent(object : ValueEventListener {

                override fun onDataChange(doctorList: DataSnapshot) {
                    try {
                        completeAppointArrayList?.clear()
                        for (eachDoctor in doctorList.children) {
                            Log.e("TAG", "onDataChange: " + eachDoctor.value.toString())
                            var patientsListModel: CheckPatientModel = eachDoctor.getValue(CheckPatientModel::class.java)!!
                            completeAppointArrayList!!.add(patientsListModel)
                        }

                        if (completeAppointArrayList == null || completeAppointArrayList!!.size == 0) {
                            txtNoCompAppointFound!!.visibility = View.VISIBLE
                            txtNoCompAppointFound.text = "You have no recent appointment!!"
                            pbCompAppointment!!.visibility = View.GONE
                        } else {
                            txtNoCompAppointFound.visibility = View.GONE
                            pbCompAppointment!!.visibility = View.GONE
                        }

                        mCompleteAppointmentAdapter = CompleteAppointmentAdapter(activity, completeAppointArrayList)
                        rvCompletedAppoint!!.adapter = mCompleteAppointmentAdapter


                    } catch (e: Exception) {
                        Log.e("TAG", "onDataChange: $e")

                    } catch (e: Exception) {
                        Log.e("TAG", "onDataLoad: $e")
                        pbCompAppointment!!.visibility = View.GONE
                    }
                }

                override fun onCancelled(error: DatabaseError) {
                    Log.w(ContentValues.TAG, "loadPost:onCancelled", error.toException())
                }
            })
}

Here is the structure of my Firebase DB, between the Current and Previous Date at least I'll get the record of 23-4-2021 but it's not showing.

Answer 1

Your "date" property is stored in the database as a String. This means that when you call "orderByChild("date")" the results will be ordered lexicographically. This means that Strings don't consider any sort of numeric values when sorting, especially when it comes to the dates, even if the dates contain numbers "23-4-2021". So you cannot use String values when querying your database:

.startAt(previousDate).endAt(currentDate)

Instead of Timestamp objects and expect to behave as it was a date. To solve this, you should change the type of your "date" property in the database to be of type Timestamp, and add it accordingly in the database as explained in my answer from the following post:

• How to save the current date/time when I add new value to Firebase Realtime Database

Once you do that change, your code might remain unchanged and you'll get the desired results.