<div class="htmledit\_views" id="content\_views"> <p> 函数内指定是minute，则最终结果value值的单位是分钟，如果函数内指定为hours，则最终结果value值单位为小时。</p> <pre class="has" name="code"><code class="hljs sql"><ol class="hljs-ln"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">//UPLOAD\_TIME 减去 CREATE\_DTTM 求得时间差，以分钟数计时</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> <span class="hljs-keyword">avg</span>(<span class="hljs-keyword">TIMESTAMPDIFF</span>(<span class="hljs-keyword">MINUTE</span>,CREATE\_DTTM,UPLOAD\_TIME)) <span class="hljs-keyword">value</span>,<span class="hljs-keyword">LEFT</span>(CREATE\_DTTM,<span class="hljs-number">10</span>) <span class="hljs-keyword">time</span> <span class="hljs-keyword">from</span> 表名</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">WHERE</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> CREATE\_DTTM >= <span class="hljs-string">'2018-01-21'</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">AND</span> CREATE\_DTTM <=<span class="hljs-keyword">DATE\_ADD</span>( <span class="hljs-string">'2019-02-19'</span>,<span class="hljs-built\_in">INTERVAL</span> <span class="hljs-number">1</span> <span class="hljs-keyword">DAY</span>) <span class="hljs-keyword">and</span> UPLOAD\_TIME <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">LEFT</span>(CREATE\_DTTM,<span class="hljs-number">10</span>)</div></div></li></ol></code><div class="hljs-button signin" data-title="登录后复制" onclick="hljs.signin(event)"></div></pre> <p>参考：<a href="https://blog.csdn.net/yiifaa/article/details/78611079" rel="nofollow">https://blog.csdn.net/yiifaa/article/details/78611079</a></p> <p><a href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function\_microsecond" rel="nofollow">https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function\_microsecond</a></p> <p><img alt="" class="has" height="406" src="https://img-blog.csdnimg.cn/20190304150917468.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODc1MDA4NA==,size\_16,color\_FFFFFF,t\_70" width="1200"></p> <p> </p> <pre class="has" name="code"><code class="hljs java"><ol class="hljs-ln"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">mysql> <span class="hljs-function">SELECT <span class="hljs-title">TIMESTAMPDIFF</span><span class="hljs-params">(MONTH,<span class="hljs-string">'2003-02-01'</span>,<span class="hljs-string">'2003-05-01'</span>)</span></span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> -> <span class="hljs-number">3</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">mysql> <span class="hljs-function">SELECT <span class="hljs-title">TIMESTAMPDIFF</span><span class="hljs-params">(YEAR,<span class="hljs-string">'2002-05-01'</span>,<span class="hljs-string">'2001-01-01'</span>)</span></span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> -> -<span class="hljs-number">1</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">mysql> <span class="hljs-function">SELECT <span class="hljs-title">TIMESTAMPDIFF</span><span class="hljs-params">(MINUTE,<span class="hljs-string">'2003-02-01'</span>,<span class="hljs-string">'2003-05-01 12:05:55'</span>)</span></span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> -> <span class="hljs-number">128885</span></div></div></li></ol></code><div class="hljs-button signin" data-title="登录后复制" onclick="hljs.signin(event)"></div></pre> <p>原文地址：https://blog.csdn.net/weixin\_38750084/article/details/88118039</p> </div>
