这里总结一下mysql中日期常用函数:
1、NOW():返回但前日期和时间,格式yyyy-mm-dd hh:mm:ss
SELECT NOW();
+---------------------+
| now() |
+---------------------+
| 2016-01-20 21:06:20 |
+---------------------+
2、CURDATE():返回但前日期,格式 yyyy-mm-dd
SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2016-01-20 |
+------------+
3、 CURTIME():返回当前时间,格式 hh:mm:ssSELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 21:11:19 |
+-----------+
4、 DATE(datetime):提取日期时间格式中的日期,格式 yyyy-mm-ddSELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2016-01-20 |
+-------------+
5、 EXTRACT(xxxx FROM datetime):返回日期时间中的单独部分xxxx选项:
YEAR、MONTH、DAY、WEEK、HOUR、MINUTE、SECOND、YRAY_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、DAY_MICROSECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND、MINUTE_MICROSECOND、QUARTER、MICROSECOND
SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(WEEK FROM NOW());
+--------------------------+---------------------------+-------------------------+--------------------------+
| EXTRACT(YEAR FROM NOW()) | EXTRACT(MONTH FROM NOW()) | EXTRACT(DAY FROM NOW()) | EXTRACT(WEEK FROM NOW()) |
+--------------------------+---------------------------+-------------------------+--------------------------+
| 2016 | 1 | 20 | 3 |
+--------------------------+---------------------------+-------------------------+--------------------------+
6、 DATE_ADD(datetime,INTERVAL expr type):向日期添加指定间隔添加间隔类型type参数:
同EXTRACT()中xxxx
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); 天数加一
+--------------------------------+