Home >Database >Mysql Tutorial >mySQL的datetime的时间间隔_MySQL

mySQL的datetime的时间间隔_MySQL

WBOY
WBOYOriginal
2016-06-01 13:18:25727browse

bitsCN.com

mySQL比较两个datetime类型的时间间隔,以秒为单位:

SELECTTIME_TO_SEC(TIMEDIFF('2009-02-09 11:24:46','2009-02-09 10:23:46'));

先看SELECT TIMEDIFF('2009-02-09 11:24:46','2009-02-09 10:23:46')
/01:01:00
SELECT TIME_TO_SEC('01:01:00')
/3660
因此便知两个datetime数据之间相差的秒数。
如果我们考虑这两个datetime数据先用TIME_TO_SEC转化为各自秒数,再来相减,不是一样的效果吗?
SELECT TIME_TO_SEC('2009-02-09 11:24:46')-TIME_TO_SEC('2009-02-09 10:23:46')
3660
看似这种方法也ok,不过我们再观察一组数据,你就清楚为什么不能用这种方式来比较两个datetime数据之间的时间间隔了。
我们现在改用&#39;2009-02-<strong>08</strong> 11:24:46&#39;和&#39;2009-02-09 10:23:46&#39;比较:
SELECT TIME_TO_SEC(&#39;2009-02-<strong>08</strong> 11:24:46&#39;)-TIME_TO_SEC(&#39;2009-02-09 10:23:46&#39;)
3660
奇怪了,左边参数已经比右边参数提早一天了,为什么秒数相减还是正值呢?
原来,TIME_TO_SEC只会把datetime数据的time部分转化为秒数,不会关心date谁大谁小,所以要比较两个datetime数据,先得TimeDiff一下,再转化为秒数,即开头写的:
<strong>SELECT TIME_TO_SEC(TIMEDIFF(&#39;2009-02-09 11:24:46&#39;,&#39;2009-02-09 10:23:46&#39;));</strong>
bitsCN.com
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn