按日期对小组进行分组，忽略小时/分钟/秒 [英] Sequelize grouping by date, disregarding hours/minutes/seconds

问题描述

嘿所以我试图从数据库中查询，使用Sequelize（Node.js ORM for postgreSQL），我试图按日期范围分组，并保持该表中多少项的计数。

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-订单和组

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

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