MySQL日期时间函数

最新推荐文章于 2021-02-17 19:47:04 发布

北极光的征兆_liekkas

最新推荐文章于 2021-02-17 19:47:04 发布

阅读量324

点赞数

分类专栏： mysql数据库

本文链接：https://blog.csdn.net/Rbaggio92/article/details/52900507

版权

mysql数据库专栏收录该内容

35 篇文章 2 订阅

订阅专栏

日期时间函数

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)