mysql日期时间函数(常用的)

mysql> SELECT NOW();  #返回（打印）当前日期和时间
+---------------------+
| NOW() |
+---------------------+
| 2017-03-28 17:53:34 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT CURDATE();   #返回（打印）当前日期
+------------+
| CURDATE() |
+------------+
| 2017-03-28 |
+------------+

mysql> SELECT CURTIME();   #返回（打印）当前时间
+-----------+
| CURTIME() |
+-----------+
| 17:55:57 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2014-3-12', INTERVAL 365 DAY);  #日期变化
+-----------------------------------------+
| DATE_ADD('2014-3-12', INTERVAL 365 DAY) |
+-----------------------------------------+
| 2015-03-12 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2014-3-12', INTERVAL -365 DAY);
+------------------------------------------+
| DATE_ADD('2014-3-12', INTERVAL -365 DAY) |
+------------------------------------------+
| 2013-03-12 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2014-3-12', INTERVAL 1 YEAR);
+----------------------------------------+
| DATE_ADD('2014-3-12', INTERVAL 1 YEAR) |
+----------------------------------------+
| 2015-03-12 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2014-3-12', INTERVAL 3 WEEK);
+----------------------------------------+
| DATE_ADD('2014-3-12', INTERVAL 3 WEEK) |
+----------------------------------------+
| 2014-04-02 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2013-3-12','2014-3-12');  #日期差值
+-----------------------------------+
| DATEDIFF('2013-3-12','2014-3-12') |
+-----------------------------------+
| -365 |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_FORMAT('2014-3-2', '%m/%d/%Y');  #日期格式化
+-------------------------------------+ 
| DATE_FORMAT('2014-3-2', '%m/%d/%Y') |
+-------------------------------------+
| 03/02/2014 |
+-------------------------------------+
1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/toudoubao/p/6636150.html