CREATE TABLE Test (
id int primary key,
Present varchar(10),
Date date,
Time time
);
INSERT INTO Test (id, Present, Date, Time)
Values (1, 'Present', '2018-07-18', '10:13:55' ),
(2, 'Present', '2018-07-18', '10:10:55' );
查询:
SELECT
id,
Present,
Date,
Time,
TIMESTAMPDIFF(MINUTE, time, CURRENT_TIMESTAMP())
FROM Test
结果:
| id | Present | Date | Time | TimeDifference |
|----|---------|------------|----------|----------------|
| 1 | Present | 2018-07-18 | 10:13:55 | -127 |
| 2 | Present | 2018-07-18 | 10:10:55 | -124 |
我希望将时间差异显示为hh:mm:ss而不仅仅是分钟。我试过:
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, time, CURRENT_TIMESTAMP())) AS TimeDifference
但这给了我一个错误:“第5列中的时间格式不正确”-02:03:10”
如何从TIMESTAMPDIFF函数中提取此信息我在网上到处找,但什么也找不到。