10

I just started using Sails.js with its ORM, Waterline, and absolutely love it, but I am not sure how to use query modifiers for dates. I am using sails-mysql. Specifically, I am trying to get rows that have a datetime field between two specific dates. I have tried doing this:

MyModel.find()
    .where({ datetime_field: { '>=': startDate } })
    .where({ datetime_field: { '<=': endDate } })
    .done(function(err, objects){
        // ...
    });

startDate and endDate are two Date objects. I have also tried converting them to strings with toString(). In both cases, I get every row from the database instead of rows between the two dates. Is there a way to do this or is this functionality not yet part of either Waterline or sails-mysql?

Travis Webb
  • 14,688
  • 7
  • 55
  • 109
lmanco
  • 131
  • 1
  • 7

2 Answers2

9

In the same boat (no pun intended) but using sails-mongo.

Provided you have the correct date formatting (as Jeremie mentions). I personally store dates in UTC moment(startDate).toISOString() on the client you can moment(startDate) to work in local date and time.

Looking at the Waterline source for deferred queries (see the where method) it applies the most recent datetime_field criteria it finds to be valid.

After trawling through code, searches and the group I didn't find anything that helped. I sure hope I've missed something obvious, but for now my current suggestion would be to anchor the results on your startDate and then cap it with a limit,

e.g.

.where({ datetime_field: { '>=': startDate } })
.limit(100)
Shane Maiolo
  • 171
  • 4
7

You will need to format your date as a string in the YYYY-MM-DD format. If you need help formatting your dates, moment.js has a lot of cool features for that.

formating your date would look something like 

var formatedStartDate = moment(startDate).format('YYYY-MM-DD');
Jérémie Parker
  • 3,184
  • 2
  • 20
  • 33