Firebase query to compare dates which is a string to the actual date

July 16, 2022

I mistakenly set a date field as a string when creating the database which i didn’t realize and now the documents in the db has really increased so it stores the date number as a string in the firestore database and i want to make a query where i compare if the date is between the start and end date i provided. can anyone help me out?

this is how i stored it in the docmument

date: Date.now().toString()

and the variables for the start date and end date

  startDate = new Date(new Date().getFullYear(), new Date().getMonth(), 1);
  endDate = new Date(
  new Date().getFullYear(),
  new Date().getMonth() + 1,
   0 ).setHours(23, 59, 59, 999);

and this is the query

return this.firestore
  .collection('orders', (orders) =>
    orders
      .where('date', '>=', this.startDate)
      .where('date', '<=', this.endDate)
      .orderBy('date', 'desc')
  )

this doesn’t work, is there a way i can get around this? or i have to restructure the db again?

>Solution :

Storing a date you want to query on like this is a bad idea and a common anti-pattern:

date: Date.now().toString()

The format that you get from calling toString() ("Wed Oct 05 2011 07:48:00 GMT-0700 (Mountain Standard Time)") is meant for displaying to humans and not suitable for ordering and filtering by code.

You should either store your values as Firestore Timestamp, or as a string format that can be ordered and filtered.

Storing dates as a Timestamp can be accomplished with:

date: Date.now()

With this Firestore will store the value as a Timestamp, which you can then order and filter on with ease by passing either Timestamp or Date objects to the where clauses of your query.

Storing dates as a lexicographically ordered string can be accomplished with:

date: Date.now().toISOString()

With this you will be storing strings in a format like "2011-10-05T14:48:00.000Z", which can be sorted and be used to perform range queries.

Update: As you pointed out, your Date.now().toString() gives you a numeric timestamp in string format, e.g. "1657889475842".

In that case the problem is that you’re passing Date objects in the query, and comparing a date to a string will never give a match. You should either pass numbers-as-strings to the where too, or take the approach I recommended above.