Sequelize 按日期分组，不考虑小时/分钟/秒

问题描述

嘿，所以我试图从数据库中查询，使用 Sequelize(用于 postgreSQL 的 Node.js ORM)，我试图按日期范围分组，并计算该表中有多少项目.

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.

现在我的代码是

 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.

我假设它必须是类似 sequelize.fn(...)

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

推荐答案

如你所说，用 sequelize.fn(...) 完成，没有别的办法.试试:

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 ;)

请注意，PostgreSQL 允许您截断到特定的时间间隔.欲了解更多信息，请访问:http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

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

另外，要了解组(和顺序)的工作原理，请参阅 Sequelize 的文档:https://github.com/sequelize/sequelize/blob/172272c8be9a847b2d64f0158826738703befddf/docs/docs/models-usage.md#manipulating-the-dataset-with-limit-offset-order-and-group

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

这篇关于Sequelize 按日期分组，不考虑小时/分钟/秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！