skip to Main Content

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?

2

Answers


  1. Chosen as BEST ANSWER

    I fixed it. This is the solution

    return this.firestore
      .collection('orders', (orders) =>
        orders
          .where('date', '>=', this.startDate.getTime().toString())
          .where('date', '<=', this.endDate.toString())
      )
      .valueChanges({ idField: 'Id' });
    

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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search