MySQL日期时间函数

日期时间函数

NOW()                                            当前日期和时间

CURDATE()                                   当前日期

CURTIME()                                    当前时间

DATE_ADD()                                 日期变化

DATEDIFF()                                  日期差值

DATE_FORMAT()                         日期格式化

例1:打印当前日期和时间

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-10-23 10:58:44 |
+---------------------+
1 row in set (0.01 sec)

例2:打印当前日期

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-23 |
+------------+
1 row in set (0.00 sec)

例3:打印当前时间

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 11:00:16  |
+-----------+
1 row in set (0.00 sec)

例4:日期变化，加时间

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.01 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 1 WEEK);
+---------------------------------------+
| DATE_ADD('2014-3-12',INTERVAL 1 WEEK) |
+---------------------------------------+
| 2014-03-19                            |
+---------------------------------------+
1 row in set (0.00 sec)

例5:日期变化，减时间

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)

例6:计算两个时间之间的差值

mysql> SELECT DATEDIFF('2013-3-12','2012-3-12');
+-----------------------------------+
| DATEDIFF('2013-3-12','2012-3-12') |
+-----------------------------------+
|                               365 |
+-----------------------------------+
1 row in set (0.01 sec)

例7:日期格式化

mysql> SELECT DATE_FORMAT('2014-3-2','%m/%d%Y');

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

| DATE_FORMAT('2014-3-2','%m/%d%Y') |

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

| 03/022014                         |

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

1 row in set (0.00 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)