+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
4.subtime(d,t):起始时间d减去时间t
mysql> select subtime('1997-12-31 23:59:50','00:00:05'), subtime('23:59:50','00:00:05');
+-------------------------------------------+--------------------------------+
| subtime('1997-12-31 23:59:50','00:00:05') | subtime('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45 | 23:59:45 |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
5.datediff(d1,d2):返回起始时间d1和结束时间d2之间的天数
mysql> select datediff('1997-12-31 23:59:59','1997-12-30');
+----------------------------------------------+
| datediff('1997-12-31 23:59:59','1997-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.00 sec)
6.date_format(date,format):根据format字符串显示date值的格式
mysql> select date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s');
+---------------------------------------------------------+
| date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
7.str_to_date(str,format) 字符串转化为时间
mysql> select str_to_date('04/31/2004', '%m/%d/%y %h:%i:s');
+-----------------------------------------------+
| str_to_date('04/31/2004', '%m/%d/%y %h:%i:s') |
+-----------------------------------------------+
| 2004-04-31 00:00:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
8.timestamp(expr) , timestamp(expr,expr2) :
对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr 中，将theresult作为日期时间值返回
mysql> select timestamp('2003-12-31'), timestamp('2003-12-31 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| timestamp('2003-12-31') | timestamp('2003-12-31 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)
9.取当天0点0分，下一天0点0分
mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));
+----------------------------+---------------------------------------+
| timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)