MySQL各种时间取数函数

来源：互联网发布：帝国cms 自动生成标签编辑：程序博客网时间：2024/05/22 02:12

1、取一个月之前的时间：

1）mysql> select date_sub(curdate(), interval 1 month); #当前日期一个月之前的日期

+---------------------------------------+
| date_sub(curdate(), interval 1 month) |
+---------------------------------------+
| 2017-03-28 |
+---------------------------------------+

2）mysql> select date_sub(now(), interval 1 month); #当前日期一个月之前的时间

+-----------------------------------+
| date_sub(now(), interval 1 month) |
+-----------------------------------+
| 2017-03-28 16:00:04 |
+-----------------------------------+

2、指定获取时间格式：

1）mysql> select date_format(date_sub(now(), interval 1 month), '%y-%m-%d'); #当前日期一个月之前是多少年月日

+------------------------------------------------------------+
| date_format(date_sub(now(), interval 1 month), '%y-%m-%d') |
+------------------------------------------------------------+
| 17-03-28 |
+------------------------------------------------------------+

2）mysql> select date_format(date_sub(now(), interval 1 month), '%Y-%M-%D'); #当前日期一个月之前是多少年月日

+------------------------------------------------------------+
| date_format(date_sub(now(), interval 1 month), '%Y-%M-%D') |
+------------------------------------------------------------+
| 2017-March-28th |
+------------------------------------------------------------+

3）mysql> select date_format(date_sub(now(), interval 1 month), '%y-%m'); # 当前日期一个月之前是多少年月

+---------------------------------------------------------+
| date_format(date_sub(now(), interval 1 month), '%y-%m') |
+---------------------------------------------------------+
| 17-03 |
+---------------------------------------------------------+

3、日期加减

1）mysql> select interval 1 month + '2017-04-28'; # 某一日期1个月之后的日期是多少

+---------------------------------+
| interval 1 month + '2017-04-28' |
+---------------------------------+
| 2017-05-28 |
+---------------------------------+

2）mysql> select '2017-04-28' + interval 10 day; # 某一日期10天之后的日期是多少

+--------------------------------+
| '2017-04-28' + interval 10 day |
+--------------------------------+
| 2017-05-08 |
+--------------------------------+

3）mysql> select '2017-04-28' - interval 10 day; # 某一日期10天之前的日期是多少

+--------------------------------+
| '2017-04-28' - interval 10 day |
+--------------------------------+
| 2017-04-18 |
+--------------------------------+

4、时间格式修改

1）mysql> select date_format(now(), '%W %M %Y'); #时间格式修改为星期月份年份格式

+--------------------------------+
| date_format(now(), '%W %M %Y') |
+--------------------------------+
| Friday April 2017 |
+--------------------------------+

2）mysql> select date_format(now(), '%H:%i:%s'); #24小时制时间显示

+--------------------------------+
| date_format(now(), '%H:%i:%s') |
+--------------------------------+
| 16:22:32 |
+--------------------------------+

3）mysql> select date_format(now(), '%h:%i:%s'); #12小时制时间显示

+--------------------------------+
| date_format(now(), '%h:%i:%s') |
+--------------------------------+
| 04:23:05 |
+--------------------------------+

0 0