Consulta com mongoose e postgres - resolvido

Programação
#1

Tenho esta query

graficos: (dataInicio, dataFim) => {

let order = [];
order.push("data_hora|asc".split("|"));
order.push("id_campanha|asc".split("|"));

const queryFilter = [

  Sequelize.where(Sequelize.col("data_hora"), {
    [Op.gte]: dataInicio + " 00:00:00",
  }),

  Sequelize.where(Sequelize.col("data_hora"), {
    [Op.lte]: dataFim + " 23:59:59",
  }),
];

const where = Object.assign({ [Op.and]: queryFilter });

return Model.count({
  where,
  group: ["data_hora", "id_campanha"],
});

},

Assim o node e mongoose executa a query:

SELECT count(*) AS "count"
FROM "Dashboard" AS "Dashboard"
WHERE ("data_hora" >= '2021-07-08 00:00:00' AND
       "data_hora" <= '2021-07-08 23:59:59')
GROUP BY "data_hora", "id_campanha";

Mas, preciso do retorno assim:

SELECT count(*) AS "count", date("data_hora"), "id_campanha"
FROM "Dashboard" AS "Dashboard"
WHERE ("data_hora" >= '2021-07-08 00:00:00' AND
       "data_hora" <= '2021-07-08 23:59:59')
GROUP BY date("data_hora"), "id_campanha"

O campo data_hora é timestamp .

Como fazer ?

#2

Para retornar os campos data_hora e id_campanha, fiz assim e funcionou

let order = [];
    order.push("data_hora|asc".split("|"));
    order.push("id_campanha|asc".split("|"));
    const queryFilter = [
      Sequelize.where(Sequelize.col("data_hora"), {
        [Op.gte]: dataInicio + " 00:00:00",
      }),
      Sequelize.where(Sequelize.col("data_hora"), {
        [Op.lte]: dataFim + " 23:59:59",
      }),
    ];
    const where = Object.assign({ [Op.and]: queryFilter });
    return Model.count({
      attributes: ["data_hora", "id_campanha"],
      where,
      group: ["data_hora", "id_campanha"],
    });

Agora preciso converter timestamp para date no mongoose

#3

Consegui assim:

let order = [];
    order.push("data_hora|asc".split("|"));
    order.push("id_campanha|asc".split("|"));
    const queryFilter = [
      Sequelize.where(Sequelize.col("data_hora"), {
        [Op.gte]: dataInicio + " 00:00:00",
      }),
      Sequelize.where(Sequelize.col("data_hora"), {
        [Op.lte]: dataFim + " 23:59:59",
      }),
    ];
    const where = Object.assign({ [Op.and]: queryFilter });
    return Model.count({
      attributes: [
        [Sequelize.literal(`DATE("data_hora")`), "date"],
        "id_campanha",
      ],
      where,
      group: ["date", "id_campanha"],
    });