postgresql 按日期连续分组，不考虑小时/分钟/秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35073918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequelize grouping by date, disregarding hours/minutes/seconds
提问by repo
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.
嘿，所以我尝试从数据库中查询，使用 Sequelize（用于 postgreSQL 的 Node.js ORM），我尝试按日期范围分组，并计算该表中的项目数。
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(...)
我假设它必须像 sequelize.fn(...)
回答by barbarity
As you said, it's done with sequelize.fn(...)and there is no other way. Try:
正如你所说，它已经完成，sequelize.fn(...)没有其他方法。尝试：
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
请注意，PostgreSQL 允许您截断到特定的间隔。更多信息请访问：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
此外，要了解组（和顺序）的工作原理，请参阅 Sequelize 的文档：https: //github.com/sequelize/sequelize/blob/172272c8be9a847b2d64f0158826738703befddf/docs/docs/models-usage.md#manipating-data-the -limit-offset-order-and-group
回答by vmf91
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'],
})
回答by Saurabh Verma
you can also try:
你也可以尝试：
group:'DATE(date_added)'
or
group:'WEEK(date_added)'
or
group:'MONTH(date_added)'
though you will have to set you sql_mode = "" since you'll otherwise get the ONLY_FULL_GROUP_BY error.
虽然你必须设置你 sql_mode = "" 因为否则你会得到 ONLY_FULL_GROUP_BY 错误。
