Using between query for full date format in Sails.JS Waterline

Question

According to Between Dates using Waterline ORM SailsJS it is possible to do a between query on dates in the Waterline adapter like the following:

User.find({
    createdAt: { '>': dateFrom, '<': dateTo }
}).exec(/* ... */);

However, this does only work with date formats like 2018-04-11. Is it somehow possible to use the full ISO8601 date format in this query: e.g. 2018-04-11T12:45:00.000Z?

EDIT: Added minimal working example code.

readDataInDateRange: function(req, res) {
    var dateFrom = req.param("dateFrom");
    if (dateFrom == null || dateFrom == ''){
        return res.badRequest();
    }
    var dateTo = req.param("dateTo");
    if (dateTo == null || dateTo == ''){
        return res.badRequest();
    }
    User.find({createdAt: { '>=': dateFrom, '<=': dateTo}})
    .exec(function afterwards(err, users) {
        if (err) {
            sails.log(err);
            return res.serverError(err);
        }
        return res.ok(users);
    });
}

Felodese

start and end are variables? – Willable

Hi. Yes, they are. Sorry for that, I've added a MWE of code to the question. – Felodese

check the response here : github.com/balderdashy/sails/issues/3543#issuecomment-181495773 – Zsa

Hi, I will check this soon and put an answer here. – Felodese

Answer 1

If you use mongodb with CLI, may practically use

    $gt

operator that show all value grater than specific value. Here we have syntax in sails.js that show Query Language with details.

grater equal operators

A basic example that we can use to show all data with criteria is something like below code.

    Model.find({ age: { '<=': 20 }})

greater equal operators

In your case we most filter by dateTime mode.

    Model.find({
          where: {
            date: {
              '<=': new Date('2018-08-24T14:56:21.774Z')
            }
          }
        });

the time '2018-08-24T14:56:21.774Z' is example and you can put all ISOformat time here.

greater and equal operator for time

If your data in database has timestamp format you need to change your queries to number instead of date-time format

    Model.find({
          where: {
            createdAt: {
              '<=': new Date('2018-08-24T14:56:21.774Z').getTime()
            }
          }
        });

Date is global object is JavaScript that can instantiated and create new time class. with .getTime() timestamp can be generated.

Query time between two other times

Or in case that the developer wants to collect data between two times, I suggest using below syntax that is correct.

    Model.find({
          where: {
            updatedAt: {
              '>': new Date('2018-08-21T14:56:21.774Z').getTime(),
              '<': new Date('2018-08-25T14:56:21.774Z').getTime()
            }
          }
        });

Checked them all and don't need worry about correction! ;)

Pother