在mysql语句中对于时间格式数据的处理，格式化时间，根据生日计算年龄，计算时间差

1.在做项目时有时会遇到表中字段为datetime查出来的时间比较奇怪，这样做

select 
	id,username,DATE_FORMAT(updatetime,'%Y %T') as time
from 
	user

DATE_FORMAT(updatetime,'%Y %T') //此格式对应的时间为2017-12-07 20:22:26

2.数据库存的生日birthd为date型，要计算年龄

select YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)
		< RIGHT(birthday,5)) age
from zn_jobseeker

3.根据两个datetime类型时间，比如createtime和updatetime计算时间差，这个是分秒结算

select
		CONCAT(floor(TIMESTAMPDIFF(SECOND,createtime,updatetime)/60),'分',
		TIMESTAMPDIFF(SECOND,createtime,updatetime)%60,'秒') usetime
		from 
		zn_jobseeker_interview

4.将秒数转换为分秒

SELECT
		CONCAT(
		floor(SUM(answer_time) / 60),
		'分',
		SUM(answer_time) % 60,
		'秒'
		) usetime
		FROM
		zn_problem_answer
		WHERE
		interviewid =

#{userId,jdbcType=VARCHAR}

5.根据两个键值排序

SELECT
problem_type,
answer,
create_time
FROM
zn_problem_answer
ORDER BY
problem_type,
create_time