求一条sql，谢谢大家！

[David_jiahuan]

start_time （标红的2） 和 end_time（标红的1） 两个字段都是 varchar 类型的；
现在想用end_time 减去 start_time 的值得到分钟数；
填写到 parking_time （标红的3）字段中；

[David_jiahuan]

引用 1 楼 qq_37170555 的回复:

create table test_tab(
start_time varchar(200),
end_time varchar(200),
parking_time BIGINT
);
insert into test_tab values('2017120809312504','2017120809362504',null)

SELECT
	*, round(
		(
			UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))
		) / 60
	) AS cnt_min
FROM
	test_tab

谢谢您！

[David_jiahuan]

引用 3 楼 lht80053623 的回复:

[quote=引用 1 楼 qq_37170555 的回复:]

create table test_tab(
start_time varchar(200),
end_time varchar(200),
parking_time BIGINT
);
insert into test_tab values('2017120809312504','2017120809362504',null)

SELECT
	*, round(
		(
			UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))
		) / 60
	) AS cnt_min
FROM
	test_tab

楼上做得对，我回答前没有注意看 一条语句

UPDATE table名
SET parking_time = round((UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))) / 60) ;

[/quote] 谢谢您！

[听雨停了]

create table test_tab(
start_time varchar(200),
end_time varchar(200),
parking_time BIGINT
);
insert into test_tab values('2017120809312504','2017120809362504',null)

SELECT
	*, round(
		(
			UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))
		) / 60
	) AS cnt_min
FROM
	test_tab

[小小爱好者]

告诉你个思路吧，自己动手，将 startTime 和 endTime 转换为时间戳，然后运算 ，将时间戳运算结果换算为分钟就OK了

[小小爱好者]

引用 1 楼 qq_37170555 的回复:

create table test_tab(
start_time varchar(200),
end_time varchar(200),
parking_time BIGINT
);
insert into test_tab values('2017120809312504','2017120809362504',null)

SELECT
	*, round(
		(
			UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))
		) / 60
	) AS cnt_min
FROM
	test_tab

楼上做得对，我回答前没有注意看 一条语句

UPDATE table名
SET parking_time = round((UNIX_TIMESTAMP(cast(end_time AS datetime)) - UNIX_TIMESTAMP(cast(start_time AS datetime))) / 60) ;