typescript how to filter data created date and expiration date using sequalize query-CodePudding

how can I filter the product whose current date is within the created date and the current date does not exceed the expiration date?

  async getAllActiveProduct(): Promise<Array<Product>> {
    const currentDate = new Date(Date.now() - 86400 * 7000);

    const product: Array<Product> = await this.findActiveProducts(
      currentDate,
      Status.active
    );
    
    return product;
  }

  async findActiveProducts(
    currentDate: Date,
    status?: Status
  ): Promise<Array<Product>> {
    const filter = {
      [Op.and]: [{ status: status}],
      [Op.between]: [{created: currentDate, expiration: currentDate }]
    };
    return result;
  }

the result of currentDate is 2022-03-04T04:19:08.791Z and the result of created field is 2022-03-04 17:08:03.188 08 the problem here is i dont know how to format the date

the error i get is

CodePudding user response：

You won't be able to use BETWEEN for this since it only operates on properties, eg WHERE property BETWEEN x AND y.

You want something like this

Product.findAll({
  where: {
    status, // status = status
    created: {
      [Op.lte]: currentDate // created is at or before currendDate
    },
    expiration: {
      [Op.gte]: currentDate // expiration is at or after currentDate
    }
  }
};

This will produce a query like

SELECT ... FROM "products"
WHERE "products"."status" = 'Active'
AND "products"."created" <= '2022-03-04T04:58:13.267Z'
AND "products"."expiration" >= '2022-03-04T04:58:13.267Z'

See https://sequelize.org/master/manual/model-querying-basics.html