MYSQL中TIMESTAMPDIFF和时间戳字段直接相减的区别_mysql 时间戳相减

使用TIMESTAMPDIFF函数与直接使用两个字段进行作差的区别

比如用$end\_time-start\_time$，那么两个时间戳的时间差的进制会是按照100的。比如相差1分钟，但查询出来的是却是100。

但是用$TIMESTAMPDIFF(second,start\_time,end\_time)$的话，两个时间戳相差超过1分钟的话，就会是60。

实验的SQL语句：

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid INT NOT NULL COMMENT '用户ID',
video_id INT NOT NULL COMMENT '视频ID',
start_time datetime COMMENT '开始观看时间',
end_time datetime COMMENT '结束观看时间',
if_follow TINYINT COMMENT '是否关注',
if_like TINYINT COMMENT '是否点赞',
if_retweet TINYINT COMMENT '是否转发',
comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
video_id INT UNIQUE NOT NULL COMMENT '视频ID',
author INT NOT NULL COMMENT '创作者ID',
tag VARCHAR(16) NOT NULL COMMENT '类别标签',
duration INT NOT NULL COMMENT '视频时长(秒数)',
release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:21', 0, 0, 1, null),
(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:25', 0, 1, 0, 1732526),
(102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null),
(103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 1, null),
(101, 2003, '2020-09-01 10:00:00', '2020-09-01 10:00:42', 1, 0, 1, null),
(102, 2003, '2021-09-01 10:00:00', '2021-09-01 10:01:06', 1, 0, 1, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
(2001, 901, '影视', 30, '2021-01-01 7:00:00'),
(2002, 901, '美食', 60, '2021-01-01 7:00:00'),
(2003, 902, '旅游', 90, '2020-01-01 7:00:00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

得到的数据是

然后查询对比

SELECT TIMESTAMPDIFF(second,tvl.start_time,tvl.end_time) as tsd ,tvl.end_time - tvl.start_time as tt
from tb_user_video_log tvl
1
2

得到结果

就是

• 2021-09-01 10:01:06与2021-09-01 10:00:00用$end\_time-start\_time$得到的结果是106，但是用$TIMESTAMPDIFF(second,start\_time,end\_time)$得到的结果是66。
• 2021-09-01 10:59:05与2021-09-01 11:00:05用$end\_time-start\_time$得到的结果是4100（相当于11:00:05-10:59:05是用100进制去减掉，因此相当于分钟数00减59的时候，要向11借一个1，换算成100*100 - 59 * 100 = 4100），但是用$TIMESTAMPDIFF(second,start\_time,end\_time)$得到的结果是60。