我有一个mysql查询，它从数据库中提取创建和修改的时间，然后将它们更改为unix时间
SELECT
tn,
title,
UNIX_TIMESTAMP(create_time) AS create_time,
UNIX_TIMESTAMP(change_time) AS change_time
FROM
ticket
WHERE
responsible_user_id = 2
AND ticket_state_id IN (2, 3, 10)
ORDER BY
change_time DESC
结果如下：
+---------+----------------------+-------------+-------------+
| tn | title | create_time | change_time |
+---------+----------------------+-------------+-------------+
| 7409025 | Re: otrs | 1459861204 | 1459861263 |
| 7408967 | [email protected] | 1459765215 | 1459836797 |
| 7408935 | FW: Vod | 1459767210 | 1459836797 |
如何从create_time行中减去change_time行，以便这些行在名为ave的新行中显示结果。
+---------+----------------------+-------------+-------------+------+
| tn | title | create_time | change_time | ave |
+---------+----------------------+-------------+-------------+------+
| 7409025 | Re: otrs | 1459861204 | 1459861263 | 59 |
| 7408967 | [email protected] | 1459765215 | 1459836797 | 71582|
| 7408935 | FW: Vod | 1459767210 | 1459836797 | 69587|
你差一点就做到了
SELECT
tn,
title,
UNIX_TIMESTAMP(create_time) AS create_time,
UNIX_TIMESTAMP(change_time) AS change_time,
(UNIX_TIMESTAMP(change_time) - UNIX_TIMESTAMP(create_time)) AS ave
FROM
ticket
WHERE
responsible_user_id = 2
AND ticket_state_id IN (2, 3, 10)
ORDER BY
change_time DESC
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句