repo repo - 6 months ago 113
SQL Question

Sequelize grouping by date, disregarding hours/minutes/seconds

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

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

Comments