Sequelize按日期分组，忽略小时/分钟/秒

Question

嘿，我正在尝试使用Sequelize（用于postgreSQL的Node.js ORM）从数据库中查询数据，我想按日期范围分组，并计算该表中有多少项。

目前我拥有的代码是：

 Task.findAll({
    attributes: ['createdAt'],
    group: 'createdAt'
  })

但是从你可以看到的内容来看，分组仅考虑确切的日期（包括秒），因此分组实际上是无意义的，因为无论如何都不会有具有完全相同的秒数的重叠项目。所以我希望它只基于天、年和月进行分组。

我假设它将类似于sequelize.fn(...)。

- repo

Answer 1

就像你所说的，它是通过 sequelize.fn(...) 完成的，没有其他方法。尝试：

Task.findAll({
  group: [sequelize.fn('date_trunc', 'day', sequelize.col('createdAt'))]
})

我认为那可能会起作用。如果不行，我们可以看看怎么做 ;)

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

此外，要理解如何使用group（和order），请参阅Sequelize文档：https://github.com/sequelize/sequelize/blob/172272c8be9a847b2d64f0158826738703befddf/docs/docs/models-usage.md#manipulating-the-dataset-with-limit-offset-order-and-group

- barbarity

---

@repo 很高兴能够帮助！您还可以为未来的观众点赞。我将包括 Sequelize 的文档以供组属性使用。 - barbarity

@barbarity 当您想要使用特定时区进行截断时，该怎么做？ - frlinw

Answer 2

所选答案在这里没有起作用。

以下方法对我有效。

Task.findAll({
    attributes: [
        [Sequelize.literal(`DATE("createdAt")`), 'date'],
        [Sequelize.literal(`COUNT(*)`), 'count']
    ],
    group: ['date'],
})

- vmf91

---

MariaDB希望在没有引号的情况下使用字段名... [Sequelize.literal（ DATE（createdAt）），'date'] - Antti A

@AnttiA 非常好。我的答案在 PostgreSQL 上进行了测试。 - vmf91

MySql 也需要没有引号的字段名，但除此之外它可以正常工作。 - cph2117

错误：函数 cms.date_trunc 不存在。 - Wang Liang

Answer 3

对于Sequelize MySQL：

我想要按照给定的年份，通过month进行group by来count数据。

以下是帮助我的代码：

Model.count({
      where: {
        createdAt: sequelize.where(
          sequelize.fn("YEAR", sequelize.col("createdAt")),
          "2022"
        ),
      },
      attributes: [
        [sequelize.fn("MONTH", sequelize.col("createdAt")), "month"],
      ],
      group: ["month"],
    })
      .then((result) => {
        console.log(result);
      })
      .catch((error) => {
        console.log(error);
      });

结果：

[
    {
        "month": 8,
        "count": 2
    },
    {
        "month": 9,
        "count": 1
    }
]

- Mohammad Zaid Pathan

---

这对我有用，如果没有找到数据，我如何拥有所有月份并计数为0。 - Donald Kagunila

无论返回哪个月份，前端（或调用API的人）都应该将其视为默认值0。或者，您可能需要在.then((result) => {之后编写自定义逻辑。 - Mohammad Zaid Pathan

Answer 4

针对Sequelize和MySQL

以下是适用于我的方法：

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

- Mr commit

Answer 5

你也可以尝试：

     group:'DATE(date_added)' 
     or
     group:'WEEK(date_added)'
     or
     group:'MONTH(date_added)'

虽然你必须将sql_mode设置为""，否则你会得到ONLY_FULL_GROUP_BY错误。

- Saurabh Verma