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?
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.
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' });