compare current year and month of date in query using sequelize

Question

I have the schema as following :

Test

const test = sequelize.define('test', {
        id: {primaryKey: true, type: DataTypes.INTEGER, autoIncrement: true, allowNull: false, unique: true},
        start_date: {type: DataTypes.DATE},
           });


    return test

Now in different rows I have start_date as 2018-05-05,2018-05-06 and 2018-06-06. Now what I want is ,I want to get the result 2018-05-05 and 2018-05-06, which are for current month and year. How should I put the query for that in sequelize?

Answer 1

If you are asking how to select dates, then as long as you are consistently working from a supplied JavaScript Date object and the DATE type for the column then simple range selection is just a matter of supplying the dates and using range operators such as $gte and $lte

const { Op } = Sequelize = require('sequelize');

let query = {
  where: {
    start_date: {
      [Op.gte]: new Date("2018-03-01"),
      [Op.lt]: new Date("2018-04-01")
    }
  }
};

let data = await DateTest.findAll(query);

Of course the preferred Symbol implementation requires a modern nodejs environment to support this in the notation of objects. If you don't have that, then you can still use "strings":

let query = {
  where: {
    start_date: {
      '$gte': new Date("2018-03-01"),
      '$lt': new Date("2018-04-01")
    }
  }
};

If you want to GROUP BY on a column with a DATE type, then the methods actually differ depending on the database engine on the backend, so that's best demonstrated with complete listings:

SQLite syntax

const { Op } = Sequelize = require('sequelize');

const log = data => console.log(JSON.stringify(data, undefined, 2));
const sequelize = new Sequelize('sqlite:dates.db', { logging: log });

const DateTest = sequelize.define('DateTest', {
  start_date: Sequelize.DATE
});

(async function() {

  try {

    await sequelize.authenticate();
    await DateTest.sync({ force: true });

    let result  = await sequelize.transaction(transaction =>
      Promise.all(
        ['2018-03-01','2018-03-01T01:12:23.000Z','2018-04-01'].map(d =>
          DateTest.create({ start_date: new Date(d) },{ transaction })
        )
      )
    );

    let query = {
      attributes: [
        [
          sequelize.fn('strftime', '%Y-%m-%d', sequelize.col('start_date')),
          'date'
        ],
        [sequelize.fn('count','*'),'count']
      ],
      group: [sequelize.col('date')],
      where: {
        start_date: {
          [Op.gte]: new Date("2018-03-01"),
          [Op.lt]: new Date("2018-04-01")
        }
      }
    };



    console.log(query);
    let data = await DateTest.findAll(query);
    log(data);

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

PostgreSQL Syntax

const { Op } = Sequelize = require('sequelize');

const uri = 'postgres://user:password@localhost/database';

const log = data => console.log(JSON.stringify(data, undefined, 2));
const sequelize = new Sequelize(uri, { logging: log });

const DateTest = sequelize.define('DateTest', {
  start_date: Sequelize.DATE
});

(async function() {

  try {

    await sequelize.authenticate();
    await DateTest.sync({ force: true });

    let result  = await sequelize.transaction(transaction =>
      Promise.all(
        ['2018-03-01', '2018-03-01T01:12:23.000Z','2018-04-01'].map(d =>
          DateTest.create({ start_date: new Date(d) },{ transaction })
        )
      )
    );

    let query = {
      attributes: [
        [
          sequelize.fn('date_trunc', 'day', sequelize.col('start_date')),
          'date'
        ],
        [sequelize.fn('count','*'),'count']
      ],
      group: [
        sequelize.col('date')
      ],
      where: {
        start_date: {
          [Op.gte]: new Date("2018-03-01"),
          [Op.lt]: new Date("2018-04-01")
        }
      }
    };



    console.log(query);
    let data = await DateTest.findAll(query);
    log(data);

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

Actually the date_trunc() should work with various SQL RDBMS back ends, but notably not with SQLite, just in case you even considered for testing purposes if not "lite" distribution.

Both forms would output:

[
  {
    "date": "2018-03-01",
    "count": 2
  }
]

Indicating that we only "selected" dates occurring in the month of March from the inserted sample with the query conditions, and that with the GROUP BY we truncated by "day" in order to achieve the expect total count of 2 for the given day.