mysql设置两个日期格式相减的方式:
原始数据表数据:
![](https://img-blog.csdn.net/20140710160854349?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2FuZ211bWluZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
select (atime - btime) sec from 数据表;
相减得到的并不是秒,特别需要注意!
结果:
![](https://img-blog.csdn.net/20140710160912679?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2FuZ211bWluZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
mysql中计算两个Datetime类型的时间间隔(单位为秒),需要转换:
1.跨天,月,年都无问题
select (UNIX_TIMESTAMP(atime) - UNIX_TIMESTAMP(btime)) sec
from 数据表;
![](https://img-blog.csdn.net/20140710160944908?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2FuZ211bWluZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
2.只能用在Time类型,跨天,月,年都有问题
select (TIME_TO_SEC(atime) - TIME_TO_SEC(btime)) sec
from 数据表;
select (TIME_TO_SEC(end_time) -TIME_TO_SEC(start_time)) sec from task_detail where end_time is not null;
结果同上图所示;
修改数据:
update task_detail set end_time = now() where id = 8;
查询结果为负值,具体见截图:
![](https://img-blog.csdn.net/20140710161426193?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2FuZ211bWluZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
故在做两个日期相减的时候,采用(TIME_TO_SEC(atime) - TIME_TO_SEC(btime)) sec 方式比较好。