Sequelize grouping by date, disregarding hours/minutes/seconds

Question

Hey so im trying to query from a database, using Sequelize (Node.js ORM for postgreSQL), im trying to group by date range, and keep a count of how many items where in that table.

Right now the code i have is

 Task.findAll({
    attributes: ['createdAt'],
    group: 'createdAt'
  })

But as you can see the grouping only takes into account the exact date (including seconds) so the grouping is actually pointless since no matter what there will be no overlapping items with the exact same second count. So i want it to just be group based on day, year and month.

Im assuming that it will have to be something like sequelize.fn(...)

Answer 1

The selected answer didn't work here.

This is what is working for me.

Task.findAll({
    attributes: [
        [Sequelize.literal(`DATE("createdAt")`), 'date'],
        [Sequelize.literal(`COUNT(*)`), 'count']
    ],
    group: ['date'],
})

Answer 2

As you said, it's done with sequelize.fn(...) and there is no other way. Try:

Task.findAll({
  group: [sequelize.fn('date_trunc', 'day', sequelize.col('createdAt'))]
})

I think that might do the job. If not, we'll see how to do it ;)

Notice that PostgreSQL allows you to truncate to specific intervals. For more information visit: http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Also, to understand how group (and order) works see the documentation of Sequelize: https://github.com/sequelize/sequelize/blob/172272c8be9a847b2d64f0158826738703befddf/docs/docs/models-usage.md#manipulating-the-dataset-with-limit-offset-order-and-group

Answer 3

For Sequelize and MYSQL

This what worked for Me:

Model.findAll({
      attributes: [
        /* add other attributes you may need from your table */
        [sequelize.fn('DATE', sequelize.col('createdAt')), 'Date']
      ],
      group: [sequelize.fn('DATE', sequelize.col('createdAt')), 'Date']
    })