[Linux运维 -- 数据库]mysql处理时间

[Linux运维 -- 数据库]mysql处理时间

基本用法：

（1）获取时间日期

• 基本时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-08-24 14:48:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-08-24 |
+------------+

• 获取星期几

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                2 |
+------------------+

mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    2 |
+----------------------+

• 获取这个月的第几天

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

• 获取这一年的第几天

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              236 |
+------------------+
1 row in set (0.00 sec)

（2）动态获取时间

• 获取一天前

mysql> select date_sub(now(), interval 1 day);
+---------------------------------+
| date_sub(now(), interval 1 day) |
+---------------------------------+
| 2015-08-23 20:06:40             |
+---------------------------------+

mysql> select date_add(now(), interval -1 day);
+----------------------------------+
| date_add(now(), interval -1 day) |
+----------------------------------+
| 2015-08-23 20:09:20              |
+----------------------------------+

• 获取一个小时前

mysql> select date_add(now(), interval -1 hour);
mysql> select date_sub(now(), interval 1 hour);

• 获取一分钟前

mysql> select date_add(now(), interval -1 minute);
mysql> select date_sub(now(), interval 1 minute);

（3） DATE_FORMAT

>select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2016-01-21 11:28:54                     |
+-----------------------------------------+

链接： http://www.w3school.com.cn/sql/func_date_format.asp

参考：

（1） http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html
（2） http://justdo2008.iteye.com/blog/1141609