mysql函数,Mysql时间函数

on 2011-9-13 in 数据库 | 0 Comment
Mysql时间函数--返回当前时间mysql> select curdate(),curtime(),now(),DATE(now()),sysdate();+------------+-----------+---------------------+-------------+---------------------+| curdate() | curtime() | now() | DATE(now()) | sysdate() |+------------+-----------+---------------------+-------------+---------------------+| 2008-12-02 | 10:11:36 | 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |+------------+-----------+---------------------+-------------+---------------------+1 row in set (0.00 sec)mysql> SELECT CURDATE(),CURDATE()+0,CURTIME(),CURTIME()+0; +------------+-------------+-----------+---------------+| CURDATE() | CURDATE()+0 | CURTIME() | CURTIME()+0 |+------------+-------------+-----------+---------------+| 2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |+------------+-------------+-----------+---------------+1 row in set (0.00 sec)--返回日期当月最后一天mysql> select last_day('2008-12-02');+------------------------+| last_day('2008-12-02') |+------------------------+| 2008-12-31 |+------------------------+1 row in set (0.00 sec)--返回日期的星期几mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');+-----------------------+-------------------------+| dayname('2008-12-02') | dayofweek('2008-12-02') |+-----------------------+-------------------------+| Tuesday | 3 |+-----------------------+-------------------------+1 row in set (0.00 sec)--返回日期的年，月，日mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');+---------------------+--------------------+-------------------+| month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |+---------------------+--------------------+-------------------+| 12 | 2008 | 2 |+---------------------+--------------------+-------------------+1 row in set (0.00 sec)--返回日期的小时，分，秒mysql> SELECT HOUR('10:05:03'),MINUTE('10:05:03'),SECOND('10:05:03'); +------------------+--------------------+--------------------+| HOUR('10:05:03') | MINUTE('10:05:03') | SECOND('10:05:03') |+------------------+--------------------+--------------------+| 10 | 5 | 3 |+------------------+--------------------+--------------------+1 row in set (0.00 sec)1.SUBDATE(d,t)：起始时间加上一段时间--返回起始时间加上N天mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY),ADDDATE('1998-01-02', 31); +-----------------------------------------+---------------------------+| DATE_ADD('1998-01-02', INTERVAL 31 DAY) | ADDDATE('1998-01-02', 31) |+-----------------------------------------+---------------------------+| 1998-02-02 | 1998-02-02 |+-----------------------------------------+---------------------------+1 row in set (0.00 sec)--返回起始时间加上年，月 mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 YEAR), DATE_ADD('1998-01-02', INTERVAL 2 MONTH); +-----------------------------------------+------------------------------------------+| DATE_ADD('1998-01-02', INTERVAL 2 YEAR) | DATE_ADD('1998-01-02', INTERVAL 2 MONTH) |+-----------------------------------------+------------------------------------------+| 2000-01-02 | 1998-03-02 |+-----------------------------------------+------------------------------------------+1 row in set (0.00 sec)--返回起始时间加上小时，加上分钟mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 hour), DATE_ADD('1998-01-02', INTERVAL 2 minute);+-----------------------------------------+-------------------------------------------+| DATE_ADD('1998-01-02', INTERVAL 2 hour) | DATE_ADD('1998-01-02', INTERVAL 2 minute) |+-----------------------------------------+-------------------------------------------+| 1998-01-02 02:00:00 | 1998-01-02 00:02:00 |+-----------------------------------------+-------------------------------------------+1 row in set (0.00 sec)2.SUBDATE(d,t)：起始时间减去一段时间mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY),SUBDATE('1998-01-02', 31);+----------------------------------------+---------------------------+| SUBDATE('1998-01-02', INTERVAL 31 DAY) | SUBDATE('1998-01-02', 31) |+----------------------------------------+---------------------------+| 1997-12-02 | 1997-12-02 |+----------------------------------------+---------------------------+1 row in set (0.00 sec)3.ADDTIME(d,t):起始时间d加入时间tmysql> SELECT ADDTIME('1997-12-31 23:59:50','00:00:05'), ADDTIME('23:59:50','00:00:05') ;+-------------------------------------------+--------------------------------+| ADDTIME('1997-12-31 23:59:50','00:00:05') | ADDTIME('23:59:50','00:00:05') |+-------------------------------------------+--------------------------------+| 1997-12-31 23:59:55 | 23:59:55 |+-------------------------------------------+--------------------------------+1 row in set (0.00 sec)4.SUBTIME(d,t):起始时间d减去时间tmysql> 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)
--取当天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)
时间函数在程序中应用非常广泛，基本上每个程序都会用到，都会和时间打交道，稍微总结了一下，这样可以减少每次去查文档的时
Tags:  mysql函数

延伸阅读

最新评论

发表评论