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
I fixed it. This is the solution
Storing a date you want to query on like this is a bad idea and a common anti-pattern:
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:With this Firestore will store the value as a
Timestamp
, which you can then order and filter on with ease by passing eitherTimestamp
orDate
objects to thewhere
clauses of your query.Storing dates as a lexicographically ordered string can be accomplished with:
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 thewhere
too, or take the approach I recommended above.