MYSQL 时间日期相关

整理下MySQL 日期时间函数及用法（5.6.*版本）

一、日期（date）相关

1、CURDATE()、CURRENT_DATE()、CURRENT_DATE：

同义，返回当前日期，如：2017-11-14。

注：

返回yyyy-MM-dd 或yyyyMMdd形式，取决于函数是否与一个分数（包括整数）进行了四则运算，进行了该运算将返回第二种形式。对CURTIME()等同理。

2、DATE(date|datetime)：

提取date或datetime 的日期部分。

3、DATE_ADD(date,INTERVAL exp unit)，DATE_SUB(date,INTERVAL exp unit)：

在日期（也可以包含时间部分）加|减"时间"，如select date_add('2017-11-13 20:20:20',interval '1:2' minute_second)结果为：2017-11-13 20:21:22。此外，第二个参数可进行四则运算，如：select '2017-11-13 20:20:20' + interval '1:2' minute_second 与上面结果相同。

注：

INTERVAL为关键字，unit 为说明符，其取值如下图：

 

4、ADDDATE(date[,intervale exp unit),SUBDATE(date[...])：

有第二个参数时，与对应的 DATE_ADD 、DATE_SUB函数相同。

5、DATE_DIFF(date1,date2)：

两个日期相减，date1 与date2 都可以是单独的日期 或日期与时间，但只有日期部分参与运算。

6、DATE_FORMAT(date,format)：

用format 格式化date，format 为格式化字符串，常用的部分格式化标识符为：

%Y：年，4位；

%y：年，2位；

%M：月，月名称，如January；

%m：月，2位（01-12）；

%D：日，如1st，2nd；

%d：日，2位（01-31）；

%H：时，2位，24小时制；

%h：时，12小时制；

%i：分，2位（00-59）；

%S或%s：秒，2位（00-59）；

%p：PM 或AM；

%r：HH:mm:ss PM形式时间；

%W：周，周名，如Tuesday；

%w：周，0-6，0为周日；

%j：当前日期所在一年中的天数，3位（001-366）。

示例：

SELECT date_format（current_date,'%Y/%m/%d'）

显示：2017/11/14

二、时间（time）相关

1、CURTIME()、CURRENT_TIME()、CURRENT_TIME：

同义，返回当前时间，如：21:35:20。返回值以当前时区表达。

三、日期和时间

1、NOW()、CURRENT_TIMESTAMP()、CURRENT_TIMESTAMP，LOCALTIME()、LOCALTIME、LOCALTIMESTAMP()、LOCALTIMESTAMP：

同义，返回当前日期和时间。

2、SYSDATE()：

系统时间，真正的系统时间，不受mysql 的SLEEP()等函数的影响。如：

 

三、全部列出来，随时可查看有哪些函数可用：

 

 

 

1、mysql DAYOFWEEK 和 WEEKDAY获取星期

在mysql中，我们可以用DAYOFWEEK 和 WEEKDAY函数获取指定日期的星期。区别在于DAYOFWEEK获取的星期索引是以1开始，而WEEKDAY获取的星期索引是以0开始.
 

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

mysql> select DAYOFWEEK('1998-02-03');

-> 3

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql> select WEEKDAY('1997-10-04 22:23:00');

-> 5

mysql> select WEEKDAY('1997-11-05');

-> 2

-- 今天

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

-- 昨天

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

-- 上周

SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';

SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

-- 本周

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';

-- 上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';

SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';

-- 上月

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';

SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';

-- 本月

SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';

SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

四、日期与时间比较大小

1、两个日期比较：

结论：两个日期可以比较大小，但不能直接相减，日期相减应该用函数DATEDIFF，该函数返回相差的天数。

 

 

2、两个时间比较：

同样，可通过>或<比较大小，也可通过时间相减函数subtime(time1,time2)。

说明：日期比较函数、时间比较函数，运算时都只是相应的部门加入运算，未提供时默认为0。

3、日期与时间都参与比较

单纯的得出大小，用 > 或 < 运算即可，要得出具体相差的时间是多少，似乎没有一个函数可直接坐到，则需要分别计算日期与时间差，最后换算为相应时间单位后相加得到最终结果。

需用到的函数：

TIME_TO_SEC(datetime)：将时间部分转化为秒数。
UNIX_TIMESTAMP(datetime)：返回时间戳