MySQL日期时间函数-CSDN博客

本文链接：https://blog.csdn.net/eandroidhu/article/details/53259694

本文介绍了MySQL中常用的日期和时间函数，包括获取当前日期、时间、完整时间和UNIX时间戳的方法，以及如何进行日期格式化和计算两个日期之间的天数差。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1,curdate

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2016-11-21 |
+------------+
1 row in set (0.01 sec)

返回当前年月日
2， curtime

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:00:14  |
+-----------+
1 row in set (0.00 sec)

返回当前时间
3，now

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-11-21 15:00:46 |
+---------------------+
1 row in set (0.00 sec)

返回年月日加时间
4, unix_timestamp

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1479711690 |
+------------------+
1 row in set (0.00 sec)

返回当前unix时间戳
5，from_unixtime

mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2016-11-21 15:03:36             |
+---------------------------------+
1 row in set (0.00 sec)

返回当前时间对应的日期，和unix_timestamp互逆
6，week, year

mysql> select week(now()), year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          47 |        2016 |
+-------------+-------------+
1 row in set (0.00 sec)

当前一年的47周，当前2016年
7, hour(), minute()

mysql> select hour(curtime()), minute(curtime());
+-----------------+-------------------+
| hour(curtime()) | minute(curtime()) |
+-----------------+-------------------+
|              15 |                 8 |
+-----------------+-------------------+
1 row in set (0.00 sec)

当前时间的15小时，当前时间的8分钟
8， monthname

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| November         |
+------------------+
1 row in set (0.00 sec)

当前日期的英文月份名称
9 date_format

mysql> select date_format(now(), '%M, %D, %Y');
+----------------------------------+
| date_format(now(), '%M, %D, %Y') |
+----------------------------------+
| November, 21st, 2016             |
+----------------------------------+
1 row in set (0.00 sec)

按照指定格式输出日期
10，datediff

mysql> select datediff('2010-09-04', now());
+-------------------------------+
| datediff('2010-09-04', now()) |
+-------------------------------+
|                         -2270 |
+-------------------------------+
1 row in set (0.00 sec)

返回2个时间相差天数