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加入时间t
mysql> 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减去时间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)
--取当天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)
时间函数在程序中应用非常广泛,基本上每个程序都会用到,都会和时间打交道,稍微总结了一下,这样可以减少每次去查文档的时间。
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Incessant 来源: Incessant
- 标签: 时间函数
- 发布时间:2009-10-11 22:34:07
- [67] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [65] Go Reflect 性能
- [61] android 开发入门
- [60] 【社会化设计】自我(self)部分――欢迎区
- [59] 图书馆的世界纪录
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [54] 视觉调整-设计师 vs. 逻辑
- [48] 读书笔记-壹百度:百度十年千倍的29条法则
- [47] 界面设计速成