Sequelize减去updatedAt和createdAt后转换为秒

Question

嘿，我真的是新来的SEQUELIZE和这个项目，我试图扣除updatedAt和createdAt值转换成秒后。

Updated and createdAt列是DATETIME格式。其中一行的createdAt值为2021-03-03 21:30:58, updatedAt值为2021-03-03 22:42:05。有没有办法先把这个值转换成秒，然后再减去。我在下面试了一下，但是它没有格式化成秒，而且timePlayed总是显示结果为14000。

谁能让我知道我应该做什么序列化格式日期成秒，然后扣除createdAt和updatedAt之间的值?

router.get(
"/sessions/average_team_time",
(req, res) => {
models.Session.findAll({
createdAt: sequelize.literal('createdAt * 100000'),
updatedAt: sequelize.literal('updatedAt * 100000')
'attributes': [
'id',
'createdAt',
'updatedAt',
[Sequelize.fn('time', Sequelize.col('updatedAt')), 'updatedAtHour'],
[Sequelize.fn('date_format', Sequelize.col('createdAt'), '%Y-%m-%d %H:%m:%S'), 'date_formed'],
[Sequelize.literal('(updatedAt - createdAt)'), 'timePlayed']
]
})
.then((sessions) => {
res.json(sessions);
})
.catch((err) => res.json(err));
}
); 

Answer 1

db.Session.findAll({
attributes: [
'id',
'createdAt',
'updatedAt',
[Sequelize.fn('TIMESTAMPDIFF', Sequelize.literal('SECOND'), Sequelize.col("createdAt"), Sequelize.col("updatedAt")), 'timePlayed']
]
})
.then(resp => res.send(resp))
.catch(error => res.send({ status: false, error: error }))

有时候，javascript比sequelzie更强大

db.Session.findAll({
// createdAt: sequelize.literal('createdAt * 100000'),
// updatedAt: sequelize.literal('updatedAt * 100000'),
attributes: [
'id',
'createdAt',
'updatedAt',
[Sequelize.fn('time', Sequelize.col('updatedAt')), 'updatedAtHour'],
[Sequelize.fn('date_format', Sequelize.col('createdAt'), '%Y-%m-%d %H:%m:%S'), 'date_formed'],
// [Sequelize.literal('(updatedAt - createdAt)'), 'timePlayed']
]
})
.then((sessions) => {
sessions = JSON.parse(JSON.stringify(sessions))
sessions = sessions || []
sessions = sessions.map(item => {
item.timePlayed = new Date(item.updatedAt).getTime() - new Date(item.createdAt).getTime()
return item;
})
res.json(sessions);
})
.catch((err) => res.json(err));

或者，我们可以使用数据类型。虚拟

模型:定义

Session.init({
timePlayed: {
type: DataTypes.VIRTUAL,
get() {
return new Date(this.updatedAt).getTime() - new Date(this.createdAt).getTime()
}
}
})
...
db.Session.findAll({
attributes: ['id', 'createdAt', 'updatedAt', 'timePlayed']
})
.then(resp => res.send(resp))
.catch(error => res.send({ status: false, error: error }))