MYSQL常用日期和时间函数

“今夜偏知春气暖,虫声新透绿窗纱”
有时我们可能会遇到这样的需求：当前时间是多少、下个月的今天是星期几、统计截止到当前日期前 3 天的收入总和等。这些需求就需要日期和时间函数来实现。
CURDATE()函数：返回当前日期，只包含年月日。

mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2007-07-11 |
+------------+
1 row in set (0.03 sec)

CURTIME()函数：返回当前时间，只包含时分秒。

mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:13:46 |
+-----------+
1 row in set (0.00 sec)

NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。

mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-07-11 14:14:06 |
+---------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(date)函数：返回日期 date 的 UNIX 时间戳。

mysql> select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1184134516 |
+-----------------------+
1 row in set (0.02 sec)

FROM_UNIXTIME （ unixtime ） 函 数 ： 返 回 UNIXTIME 时 间 戳 的 日 期 值 ， 和UNIX_TIMESTAMP(date)互为逆操作。

mysql> select FROM_UNIXTIME(1184134516) ;
+---------------------------+
| FROM_UNIXTIME(1184134516) |
+---------------------------+
| 2007-07-11 14:15:16 |
+---------------------------+
1 row in set (0.00 sec)

WEEK(DATE)和 YEAR(DATE)函数：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。

mysql> select WEEK(now()),YEAR(now());
+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
| 27 | 2007 |
+-------------+-------------+
1 row in set (0.02 sec)

HOUR(time)和 MINUTE(time)函数：前者返回所给时间的小时，后者返回所给时间的分钟。

mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+
| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+
| 14 | 18 |
+-----------------+-------------------+
1 row in set (0.00 sec)

MONTHNAME(date)函数：返回 date 的英文月份名称。

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

DATE_FORMAT(date,fmt)函数：按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期，

格式符  格式说明
%S,%s   两位数字形式的秒（00,01,...,59）
%i      两位数字形式的分（00,01,...,59）
%H      两位数字形式的小时，24 小时（00,01,...,23）
%h,%I   两位数字形式的小时，12 小时（01,02,...,12）
%k      数字形式的小时，24 小时（0,1,...,23）
%l      数字形式的小时，12 小时（1,2,...,12）
%T      24 小时的时间形式（hh:mm:ss）
%r      12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%p      AM 或 PM
%W      一周中每一天的名称（Sunday,Monday,...,Saturday）
%a      一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d      两位数字表示月中的天数（00,01,...,31）
%e      数字形式表示月中的天数（1,2，...,31）
%D      英文后缀表示月中的天数（1st,2nd,3rd,...）
%w      以数字形式表示周中的天数                     （0=Sunday,1=Monday,...,6=Saturday）
%j      以 3 位数字表示年中的天数（001,002,...,366）
%U      周（0,1,52），其中 Sunday 为周中的第一天
%u      周（0,1,52），其中 Monday 为周中的第一天
%M      月名（January,February,...,December）
%b      缩写的月名（January,February,...,December）
%m      两位数字表示的月份（01,02,...,12）
%c      数字表示的月份（1,2,...,12）
%Y      4 位数字表示的年份
%y      两位数字表示的年份
%%      直接值“%”

下面的例子将当前时间显示为“月，日，年”格式：

mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+
| July,11th,2007 |
+-------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期 date 相差 INTERVAL 时间段的日期。其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type是间隔类型，MySQL 提供了 13 种间隔类型

表达式类型   描述      格式
HOUR        小时      hh
MINUTE      分       mm
SECOND      秒   ss
YEAR        年   YY
MONTH       月   MM
DAY         日   DD
YEAR_MONTH  年和月  YY-MM
DAY_HOUR    日和小时  DD hh
DAY_MINUTE  日和分钟  DD hh:mm
DAY_ SECOND  日和秒  DD hh:mm:ss
HOUR_MINUTE  小时和分  hh:mm
HOUR_SECOND  小时和秒  hh:ss
MINUTE_SECOND  分钟和秒  mm:ss

来看一个具体的例子，在这个例子中第 1 列返回了当前日期时间，第 2 列返回距离当前日期31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
+---------------------+---------------------+------------------------+
1 row in set (0.01 sec)

同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回了当前日期时间，第 2列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。

mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数。
下面的例子计算出当前距离 2008 年 8 月 8 日的奥运会开幕式还有多少天：

mysql> select DATEDIFF('2008-08-08',now());
+------------------------------+
| DATEDIFF('2008-08-08',now()) |
+------------------------------+
| 328 |
+------------------------------+
1 row in set (0.01 sec)