My challenge involves working with datasets that include a 'timestamp' attribute. I am looking to efficiently select datasets that have timestamps starting from a specific start date up until an end date, without including the end date itself. In essence, I want to achieve this query:
dataSet WHERE timestamp >= startDate AND timestamp < endDateI am using CouchDB and aiming to execute this selection using Sequelize.
Assuming my start and end dates are formatted like this:
var startDate = '2015-07-29 00:00:00';
var endDate = '2015-07-30 00:00:00';
Any data falling within these timeframes should be included:
2015-07-29 00:00:00 (<--startDate)
2015-07-29 00:00:01
2015-07-29 00:00:02
....
2015-07-29 23:59:59 (<--my desired end-Date. It's right before endDate)
The end date itself (e.g., 2015-07-30 00:00:00) should not be part of the selection.
In attempting to achieve this, I initially used the following query:
db.answer.findAll({
where: {id: { in: [myId]},
timestamp: { between: {[startDate, endDate]}
}
This returned datasets inclusive of the start and end dates, contrary to what I needed. I only wanted data leading up to the end date without including it.
After refining my approach, I arrived at a query that better fits the criteria (dataSet >= startDate AND dataSet < endDate)
db.answer.findAll({
where: {id: { in: [myId]},
timestamp: { gte: startDate},
timestamp: { lt: endDate}
}
However, this query did not produce the intended results when executed. Unfortunately, I cannot provide specifics as I currently do not have access to the database. I will update this information once I can access the system again.
I considered setting the end date to 2015-07-29 23:59:59, but I believe there might be a more elegant solution to this issue.