linux mysql 时间_[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 |

+-----------------------------------------+

参考：