数据处理函数

电玩女神 2022-05-10 09:46 191阅读 0赞

## 1.文本处理函数 ##

比如UPPER函数是将小写变成大写。

类似的文本处理函数有：

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>调用示例</th> 
   <th>示例结果</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LEFT</td> 
   <td>LEFT(‘abc123’, 3)</td> 
   <td>abc</td> 
   <td>返回从左边取指定长度的子串</td> 
  </tr> 
  <tr> 
   <td>RIGHT</td> 
   <td>RIGHT(‘abc123’, 3)</td> 
   <td>123</td> 
   <td>返回从右边取指定长度的子串</td> 
  </tr> 
  <tr> 
   <td>LENGTH</td> 
   <td>LENGTH(‘abc’)</td> 
   <td>3</td> 
   <td>返回字符串的长度</td> 
  </tr> 
  <tr> 
   <td>LOWER</td> 
   <td>LOWER(‘ABC’)</td> 
   <td>abc</td> 
   <td>返回小写格式字符串</td> 
  </tr> 
  <tr> 
   <td>UPPER</td> 
   <td>UPPER(‘abc’)</td> 
   <td>ABC</td> 
   <td>返回大写格式字符串</td> 
  </tr> 
  <tr> 
   <td>LTRIM</td> 
   <td>LTRIM(’ abc’)</td> 
   <td>abc</td> 
   <td>将字符串左边空格去除后返回</td> 
  </tr> 
  <tr> 
   <td>RTRIM</td> 
   <td>RTRIM('abc ')</td> 
   <td>abc</td> 
   <td>将字符串右边空格去除后返回</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING</td> 
   <td>SUBSTRING(‘abc123’, 2, 3)</td> 
   <td>bc1</td> 
   <td>从字符串第2位开始截取3位字符</td> 
  </tr> 
  <tr> 
   <td>CONCAT</td> 
   <td>CONCAT(‘abc’, ‘123’, ‘xyz’)</td> 
   <td>abc123xyz</td> 
   <td>将各个字符串参数拼接成一个新的字符串</td> 
  </tr> 
 </tbody> 
</table>

比如：

mysql> SELECT LEFT('abc123',3); +------------------+ | LEFT('abc123',3) | +------------------+ | abc | +------------------+ 1 row in set mysql> select RIGHT('abc123', 3); +--------------------+ | RIGHT('abc123', 3) | +--------------------+ | 123 | +--------------------+ 1 row in set mysql> select length('abc'); +---------------+ | length('abc') | +---------------+ | 3 | +---------------+ 1 row in set mysql> select ltrim(' abc'); +---------------+ | ltrim(' abc') | +---------------+ | abc | +---------------+ 1 row in set mysql> select substring('1234567',2,3) ; +--------------------------+ | substring('1234567',2,3) | +--------------------------+ | 234 | +--------------------------+ 1 row in set mysql> select concat('a','b','c','d'); +-------------------------+ | concat('a','b','c','d') | +-------------------------+ | abcd | +-------------------------+ 1 row in set

## 2.日期和时间处理函数 ##

**常见的时间计算类型:**

<table> 
 <thead> 
  <tr> 
   <th>SECOND</th> 
   <th>秒</th> 
   <th>SECONDS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MINUTE</td> 
   <td>分钟</td> 
   <td>MINUTES</td> 
  </tr> 
  <tr> 
   <td>HOUR</td> 
   <td>时间</td> 
   <td>HOURS</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>天</td> 
   <td>DAYS</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>月</td> 
   <td>MONTHS</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>年</td> 
   <td>YEARS</td> 
  </tr> 
  <tr> 
   <td>MINUTE_SECOND</td> 
   <td>分钟和秒</td> 
   <td>“MINUTES:SECONDS”</td> 
  </tr> 
  <tr> 
   <td>HOUR_MINUTE</td> 
   <td>小时和分钟</td> 
   <td>“HOURS:MINUTES”</td> 
  </tr> 
  <tr> 
   <td>DAY_HOUR</td> 
   <td>天和小时</td> 
   <td>“DAYS HOURS”</td> 
  </tr> 
  <tr> 
   <td>YEAR_MONTH</td> 
   <td>年和月</td> 
   <td>“YEARS-MONTHS”</td> 
  </tr> 
  <tr> 
   <td>HOUR_SECOND</td> 
   <td>小时, 分钟，</td> 
   <td>“HOURS:MINUTES:SECONDS”</td> 
  </tr> 
  <tr> 
   <td>DAY_MINUTE</td> 
   <td>天, 小时, 分钟</td> 
   <td>“DAYS HOURS:MINUTES”</td> 
  </tr> 
  <tr> 
   <td>DAY_SECOND</td> 
   <td>天, 小时, 分钟, 秒</td> 
   <td>“DAYS HOURS:MINUTES:SECONDS”</td> 
  </tr> 
 </tbody> 
</table>

**常见的日期format参数如下:**

<table> 
 <thead> 
  <tr> 
   <th>%a</th> 
   <th>缩写星期名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%b</td> 
   <td>缩写月名</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td>月，数值</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>带有英文前缀的月中的天</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td>月的天，数值(00-31)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>月的天，数值(0-31)</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td>微秒</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>小时 (00-23)</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td>小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td>小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>分钟，数值(00-59)</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>年的天 (001-366)</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>小时 (0-23)</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td>小时 (1-12)</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>月名</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td>月，数值(00-12)</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM 或 PM</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td>时间，12-小时（hh:mm:ss AM 或 PM）</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td>秒(00-59)</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td>秒(00-59)</td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>时间, 24-小时 (hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td>%U</td> 
   <td>周 (00-53) 星期日是一周的第一天</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td>周 (00-53) 星期一是一周的第一天</td> 
  </tr> 
  <tr> 
   <td>%V</td> 
   <td>周 (01-53) 星期日是一周的第一天，与 %X 使用</td> 
  </tr> 
  <tr> 
   <td>%v</td> 
   <td>周 (01-53) 星期一是一周的第一天，与 %x 使用</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>星期名</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>周的天 （0=星期日, 6=星期六）</td> 
  </tr> 
  <tr> 
   <td>%X</td> 
   <td>年，其中的星期日是周的第一天，4 位，与 %V 使用</td> 
  </tr> 
  <tr> 
   <td>%x</td> 
   <td>年，其中的星期一是周的第一天，4 位，与 %v 使用</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td>年，4 位</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td>年，2 位</td> 
  </tr> 
 </tbody> 
</table>

常见的日期函数如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">调用示例</th> 
   <th align="left">结果</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">now()</td> 
   <td align="left">now()</td> 
   <td align="left">2018-09-27 14:22:48</td> 
   <td align="left">获得当前日期+时间（date + time）</td> 
  </tr> 
  <tr> 
   <td align="left">sysdate()</td> 
   <td align="left">sysdate()</td> 
   <td align="left"></td> 
   <td align="left">获得当前日期+时间（date + time）函数</td> 
  </tr> 
  <tr> 
   <td align="left">current_timestamp</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">获得当前时间戳函数</td> 
  </tr> 
  <tr> 
   <td align="left">current_timestamp()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">获得当前时间戳函数</td> 
  </tr> 
  <tr> 
   <td align="left">date_format(date,format)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">日期/时间转换为字符串</td> 
  </tr> 
  <tr> 
   <td align="left">time_format(time,format)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">日期/时间转换为字符串</td> 
  </tr> 
  <tr> 
   <td align="left">str_to_date(str, format)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">字符串转换为日期</td> 
  </tr> 
  <tr> 
   <td align="left">to_days(date)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">（日期、天数）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">from_days(days)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">（日期、天数）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">time_to_sec(time)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">（时间、秒）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">sec_to_time(seconds)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">（时间、秒）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">makedate(year,dayofyear)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">拼凑日期</td> 
  </tr> 
  <tr> 
   <td align="left">maketime(hour,minute,second)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">拼凑日期</td> 
  </tr> 
  <tr> 
   <td align="left">unix_timestamp()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">MySQL （Unix 时间戳、日期）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">unix_timestamp(date)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">MySQL （Unix 时间戳、日期）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">from_unixtime(unix_timestamp)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">MySQL （Unix 时间戳、日期）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">from_unixtime(unix_timestamp,format)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">MySQL （Unix 时间戳、日期）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left">date_add()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">为日期增加一个时间间隔</td> 
  </tr> 
  <tr> 
   <td align="left">adddate()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">可以用 date_add() 来替代</td> 
  </tr> 
  <tr> 
   <td align="left">addtime()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">可以用 date_add() 来替代</td> 
  </tr> 
  <tr> 
   <td align="left">date_sub()</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">为日期减去一个时间间隔</td> 
  </tr> 
  <tr> 
   <td align="left">datediff(date1,date2)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">日期、时间相减函数</td> 
  </tr> 
  <tr> 
   <td align="left">timediff(time1,time2)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">日期、时间相减函数</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp(date)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">date to timestamp</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp(dt,time)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">– dt + time</td> 
  </tr> 
  <tr> 
   <td align="left">timestampadd(unit,interval,datetime_expr)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">时间戳加法</td> 
  </tr> 
  <tr> 
   <td align="left">timestampdiff(unit,datetime_expr1,datetime_expr2)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">时间戳减法</td> 
  </tr> 
  <tr> 
   <td align="left">convert_tz(dt,from_tz,to_tz)</td> 
   <td align="left">示例见下</td> 
   <td align="left"></td> 
   <td align="left">MySQL 时区（timezone）转换函数</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
 </tbody> 
</table>

mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-09-27 14:22:48 | +---------------------+ 1 row in set

获得当前日期+时间（date + time）函数：sysdate()  
sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。sysdate() 日期时间函数，一般情况下很少用到。看下面的例子就明白了：

mysql> select now(), sleep(3), now(); +---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2018-09-27 14:26:19 | 0 | 2018-09-27 14:26:19 | +---------------------+----------+---------------------+ 1 row in set mysql> select sysdate(), sleep(3), sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | +---------------------+----------+---------------------+ | 2018-09-27 14:26:37 | 0 | 2018-09-27 14:26:40 | +---------------------+----------+---------------------+ 1 row in set

mysql> select current_timestamp, current_timestamp(); +---------------------+---------------------+ | current_timestamp | current_timestamp() | +---------------------+---------------------+ | 2018-09-27 14:42:44 | 2018-09-27 14:42:44 | +---------------------+---------------------+ 1 row in set

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 20080808222301 | +----------------------------------------------------+ 1 row in set mysql> select time_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | time_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 00000000222301 | +----------------------------------------------------+ 1 row in set

mysql> select str_to_date('08/09/2008', '%m/%d/%Y'); +---------------------------------------+ | str_to_date('08/09/2008', '%m/%d/%Y') | +---------------------------------------+ | 2008-08-09 | +---------------------------------------+ 1 row in set mysql> select str_to_date('08/09/08' , '%m/%d/%y'); +--------------------------------------+ | str_to_date('08/09/08' , '%m/%d/%y') | +--------------------------------------+ | 2008-08-09 | +--------------------------------------+ 1 row in set mysql> select str_to_date('08.09.2008', '%m.%d.%Y'); +---------------------------------------+ | str_to_date('08.09.2008', '%m.%d.%Y') | +---------------------------------------+ | 2008-08-09 | +---------------------------------------+ 1 row in set mysql> select str_to_date('08:09:30', '%h:%i:%s'); +-------------------------------------+ | str_to_date('08:09:30', '%h:%i:%s') | +-------------------------------------+ | 08:09:30 | +-------------------------------------+ 1 row in set mysql> select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); +---------------------------------------------------------+ | str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s') | +---------------------------------------------------------+ | 2008-08-09 08:09:30 | +---------------------------------------------------------+ 1 row in set

mysql> select from_days(733627); +-------------------+ | from_days(733627) | +-------------------+ | 2008-08-08 | +-------------------+ 1 row in set mysql> select to_days('2008-08-08'); +-----------------------+ | to_days('2008-08-08') | +-----------------------+ | 733627 | +-----------------------+ 1 row in set

mysql> select time_to_sec('01:00:00'); +-------------------------+ | time_to_sec('01:00:00') | +-------------------------+ | 3600 | +-------------------------+ 1 row in set mysql> select sec_to_time(3600); +-------------------+ | sec_to_time(3600) | +-------------------+ | 01:00:00 | +-------------------+ 1 row in set

mysql> select makedate(2001,31); +-------------------+ | makedate(2001,31) | +-------------------+ | 2001-01-31 | +-------------------+ 1 row in set mysql> select makedate(2001,32); +-------------------+ | makedate(2001,32) | +-------------------+ | 2001-02-01 | +-------------------+ 1 row in set mysql> select maketime(12,15,30); +--------------------+ | maketime(12,15,30) | +--------------------+ | 12:15:30 | +--------------------+ 1 row in set

mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1538032704 | +------------------+ 1 row in set mysql> select unix_timestamp('2008-08-08'); +------------------------------+ | unix_timestamp('2008-08-08') | +------------------------------+ | 1218124800 | +------------------------------+ 1 row in set mysql> select unix_timestamp('2008-08-08 12:30:00'); +---------------------------------------+ | unix_timestamp('2008-08-08 12:30:00') | +---------------------------------------+ | 1218169800 | +---------------------------------------+ 1 row in set mysql> select from_unixtime(1218290027); +---------------------------+ | from_unixtime(1218290027) | +---------------------------+ | 2008-08-09 21:53:47 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218124800); +---------------------------+ | from_unixtime(1218124800) | +---------------------------+ | 2008-08-08 00:00:00 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218169800); +---------------------------+ | from_unixtime(1218169800) | +---------------------------+ | 2008-08-08 12:30:00 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); +---------------------------------------------------+ | from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x') | +---------------------------------------------------+ | 2008 8th August 12:30:00 2008 | +---------------------------------------------------+ 1 row in set

mysql> select @dt; +---------------------+ | @dt | +---------------------+ | 2018-09-27 15:28:15 | +---------------------+ 1 row in set mysql> select date_add(@dt, interval 1 day); +-------------------------------+ | date_add(@dt, interval 1 day) | +-------------------------------+ | 2018-09-28 15:28:15 | +-------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 hour); +--------------------------------+ | date_add(@dt, interval 1 hour) | +--------------------------------+ | 2018-09-27 16:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 minute); +----------------------------------+ | date_add(@dt, interval 1 minute) | +----------------------------------+ | 2018-09-27 15:29:15 | +----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 second); +----------------------------------+ | date_add(@dt, interval 1 second) | +----------------------------------+ | 2018-09-27 15:28:16 | +----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 microsecond); +---------------------------------------+ | date_add(@dt, interval 1 microsecond) | +---------------------------------------+ | 2018-09-27 15:28:15.000001 | +---------------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 week); +--------------------------------+ | date_add(@dt, interval 1 week) | +--------------------------------+ | 2018-10-04 15:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 month); +---------------------------------+ | date_add(@dt, interval 1 month) | +---------------------------------+ | 2018-10-27 15:28:15 | +---------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 quarter); +-----------------------------------+ | date_add(@dt, interval 1 quarter) | +-----------------------------------+ | 2018-12-27 15:28:15 | +-----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 year); +--------------------------------+ | date_add(@dt, interval 1 year) | +--------------------------------+ | 2019-09-27 15:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval -1 day); +--------------------------------+ | date_add(@dt, interval -1 day) | +--------------------------------+ | 2018-09-26 15:28:15 | +--------------------------------+ 1 row in set

mysql> set @dt = '2008-08-09 12:12:33'; Query OK, 0 rows affected mysql> select date_add(@dt, interval '01:15:30' hour_second); +------------------------------------------------+ | date_add(@dt, interval '01:15:30' hour_second) | +------------------------------------------------+ | 2008-08-09 13:28:03 | +------------------------------------------------+ 1 row in set mysql> select date_add(@dt, interval '1 01:15:30' day_second); +-------------------------------------------------+ | date_add(@dt, interval '1 01:15:30' day_second) | +-------------------------------------------------+ | 2008-08-10 13:28:03 | +-------------------------------------------------+ 1 row in set

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second); +----------------------------------------------------------------+ | date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 1997-12-30 22:58:59 | +----------------------------------------------------------------+ 1 row in set

mysql> select datediff('2008-08-08', '2008-08-01'); +--------------------------------------+ | datediff('2008-08-08', '2008-08-01') | +--------------------------------------+ | 7 | +--------------------------------------+ 1 row in set mysql> select datediff('2008-08-01', '2008-08-08'); +--------------------------------------+ | datediff('2008-08-01', '2008-08-08') | +--------------------------------------+ | -7 | +--------------------------------------+ 1 row in set

mysql> select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); +--------------------------------------------------------+ | timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00') | +--------------------------------------------------------+ | 08:08:08 | +--------------------------------------------------------+ 1 row in set -- 注意：timediff(time1,time2) 函数的两个参数类型必须相同。 mysql> select timediff('08:08:08', '00:00:00'); +----------------------------------+ | timediff('08:08:08', '00:00:00') | +----------------------------------+ | 08:08:08 | +----------------------------------+ 1 row in set

mysql> select timestamp('2008-08-08'); +-------------------------+ | timestamp('2008-08-08') | +-------------------------+ | 2008-08-08 00:00:00 | +-------------------------+ 1 row in set mysql> select timestamp('2008-08-08 08:00:00', '01:01:01'); +----------------------------------------------+ | timestamp('2008-08-08 08:00:00', '01:01:01') | +----------------------------------------------+ | 2008-08-08 09:01:01 | +----------------------------------------------+ 1 row in set mysql> select timestamp('2008-08-08 08:00:00', '10 01:01:01'); +-------------------------------------------------+ | timestamp('2008-08-08 08:00:00', '10 01:01:01') | +-------------------------------------------------+ | 2008-08-18 09:01:01 | +-------------------------------------------------+ 1 row in set mysql> select timestampadd(day, 1, '2008-08-08 08:00:00'); +---------------------------------------------+ | timestampadd(day, 1, '2008-08-08 08:00:00') | +---------------------------------------------+ | 2008-08-09 08:00:00 | +---------------------------------------------+ 1 row in set mysql> select date_add('2008-08-08 08:00:00', interval 1 day); +-------------------------------------------------+ | date_add('2008-08-08 08:00:00', interval 1 day) | +-------------------------------------------------+ | 2008-08-09 08:00:00 | +-------------------------------------------------+ 1 row in set mysql> select timestampdiff(year,'2002-05-01','2001-01-01'); +-----------------------------------------------+ | timestampdiff(year,'2002-05-01','2001-01-01') | +-----------------------------------------------+ | -1 | +-----------------------------------------------+ 1 row in set mysql> select timestampdiff(day ,'2002-05-01','2001-01-01'); +-----------------------------------------------+ | timestampdiff(day ,'2002-05-01','2001-01-01') | +-----------------------------------------------+ | -485 | +-----------------------------------------------+ 1 row in set -- MySQL timestampadd() 函数类似于 date_add() mysql> select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); +-----------------------------------------------------------------+ | timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00') | +-----------------------------------------------------------------+ | -12 | +-----------------------------------------------------------------+ 1 row in set mysql> select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); +--------------------------------------------------------+ | datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00') | +--------------------------------------------------------+ | 7 | +--------------------------------------------------------+ 1 row in set

mysql> select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); +-------------------------------------------------------+ | convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00') | +-------------------------------------------------------+ | 2008-08-08 04:00:00 | +-------------------------------------------------------+ 1 row in set

-- 时区转换也可以通过 date_add, date_sub, timestampadd 来实现 mysql> select date_add('2008-08-08 12:00:00', interval -8 hour); +---------------------------------------------------+ | date_add('2008-08-08 12:00:00', interval -8 hour) | +---------------------------------------------------+ | 2008-08-08 04:00:00 | +---------------------------------------------------+ 1 row in set mysql> select date_sub('2008-08-08 12:00:00', interval 8 hour); +--------------------------------------------------+ | date_sub('2008-08-08 12:00:00', interval 8 hour) | +--------------------------------------------------+ | 2008-08-08 04:00:00 | +--------------------------------------------------+ 1 row in set mysql> select timestampadd(hour, -8, '2008-08-08 12:00:00'); +-----------------------------------------------+ | timestampadd(hour, -8, '2008-08-08 12:00:00') | +-----------------------------------------------+ | 2008-08-08 04:00:00 | +-----------------------------------------------+ 1 row in set

## 3.数值处理函数 ##

<table> 
 <thead> 
  <tr> 
   <th>Abs()</th> 
   <th>返回一个数的绝对值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Cos()</td> 
   <td>返回一个角度的余弦</td> 
  </tr> 
  <tr> 
   <td>Sin()</td> 
   <td>返回一个角度的正弦</td> 
  </tr> 
  <tr> 
   <td>Tan()</td> 
   <td>返回一个角度的正切</td> 
  </tr> 
  <tr> 
   <td>Exp()</td> 
   <td>返回一个数的指数值</td> 
  </tr> 
  <tr> 
   <td>Mod()</td> 
   <td>返回除操作的余数</td> 
  </tr> 
  <tr> 
   <td>Pi()</td> 
   <td>返回圆周率</td> 
  </tr> 
  <tr> 
   <td>Rand()</td> 
   <td>返回一个随机数</td> 
  </tr> 
  <tr> 
   <td>Sqrt()</td> 
   <td>返回一个数的平方根</td> 
  </tr> 
 </tbody> 
</table>