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(...)

Solution 1

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

Solution 2

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'],
})

Solution 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']
    })

Comments

[barbarity]

@repo glad to help! You can also upvote the answer for future viewers. I'm going to include the documentation of sequelize for the group property

[frlinw]

@barbarity how do you do when you want to truncate with a specific timezone?

[Antti A]

MariaDB wanted fieldname without quotes... ` [Sequelize.literal(DATE(createdAt)), 'date']`

[vmf91]

@AnttiA very well. My answer was tested on PostgreSQL.

[cph2117]

MySql also needs fieldname without quotes, but otherwise it works

[Yang Yu]

Error: FUNCTION cms.date_trunc does not exist