sequelize处理日期格式化

使用sequelize查询日期返回的默认格式如下

2020-08-21T12:23:09Z

报文输出

Copy
{
  "status": 200,
  "message": "",
  "data": {
    "list": [
      {
        "id": 40,
        "serial_no": "2dad2c69-5a2a-411d-88ce-94177e4f5c9d",
        "title": "协议",
        "status": "published",
        "version": 1,
        "memo": "协议备注",
        "created_time": "2020-08-20T09:11:12.668Z",
        "updated_time": "2020-08-20T09:11:12.668Z"
      }
    ]
  }
}

我期望的是返回，如下格式

YYYY-MM-dd HH:mm:ss

方案一#

查询了很多方案说在日期的字段里加上get方法处理日期格式化，但是在我的查询语句里并不会触发get方法

Copy
{
      created_time: {
      type: DataTypes.DATE(6),
      field: 'agreement_created_time',
      allowNull: true,
      defaultValue: sequelize.NOW,
      comment: '创建时间',
      get () {
        // console.log(this.getDataValue('created_time'))
        return this.getDataValue('created_time') ? moment(this.getDataValue('created_time')).format('YYYY-MM-DD HH:mm:ss') : null;
      }
    },
}

方案二#

在查询语句里使用sequelize.Sequelize.fn('date_format'对日期进行格式化

这里要特别注意模板 ** %Y-%m-%d %H:%i:%s **

Copy
const list = await AgreementTable.findAll({
        attributes: {
            exclude: ['deleted_time', 'content'],
            include: [
                [sequelize.Sequelize.fn('date_format', sequelize.Sequelize.col('agreement_created_time'), '%Y-%m-%d %H:%i:%s'), 'created_time'],
                [sequelize.Sequelize.fn('date_format', sequelize.Sequelize.col('agreement_updated_time'), '%Y-%m-%d %H:%i:%s'), 'updated_time'],
            ]
        },
        where,
        order: [
            ['updated_time', 'DESC']
        ]
    })

最终输出格式如下：

Copy
{
  "status": 200,
  "message": "",
  "data": {
    "list": [
      {
        "id": 40,
        "serial_no": "2dad2c69-5a2a-411d-88ce-94177e4f5c9d",
        "title": "协议",
        "status": "published",
        "version": 1,
        "memo": "协议备注",
        "created_time": "2020-08-20 17:11:12",
        "updated_time": "2020-08-20 17:11:12"
      }
    ]
  }
}