MYSQL中对时间的处理函数(DATE_FORMAT(),DATE_ADD() / DATE_SUB(),DATEDIFF（）,NIX_TIMESTAMP(),FROM_UNIXTIME())用法详解

1.DATE_FORMAT()函数

DATE_FORMAT函数格式化日期，是处理日期最常用的一个函数。
SELECT DATE_FORMAT(date,format);
#date 日期，一般是NOW()或者需要格式化的字段
#format 规定日期/时间的输出格式，
下表就是具体的格式代表的含义：

格式	代表的含义
%Y,%X,%x	年，(例：2020)
%m	月，数值(00-12)
%d	日，数值(00-31)
%H	时，数值 (00-23)
%i	分，数值(00-59)
%s,%S	秒，数值(00-59)
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%e	月的天，数值(0-31)
%f	微秒
%h,%I	小时 (01-12)
%k	小时 (0-23)
%j	年的天 (001-366)
%M	月名
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-52) 星期日是一周的第一天
%u	周 (00-52) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名(Sunday,Monday)
%w	周的天 （0=星期日, 6=星期六）
%y	年，2 位

例如1：对当前时间格式化

例如2：日期格式非标准格式时，需换换格式

查询当前小时的数据
SELECT * FROM loan_amount WHERE data_date = DATE_FORMAT(NOW(),’%Y%m%d%H’);

查询当天的数据：SUBSTR(data_date,1,8) 表示从第一位截取data_date字段共截取八位
SELECT * FROM loan_amount WHERE SUBSTR(data_date,1,8) = DATE_FORMAT(NOW(),’%Y%m%d’);

例如3：如有一个明细表（每秒数据），需要求每分钟每小时每天汇总数据时：
时间格式：create_time:2020-06-23 16:06:44
求每天汇总数据：
SELECT DATE_FORMAT(create_time,’%Y-%m-%d’),SUM(fq_amount)
FROM loan_amount GROUP BY DATE_FORMAT(create_time,’%Y-%m-%d’);
求当天每小时汇总数据：
SELECT DATE_FORMAT(create_time,’%Y-%m-%d’),SUM(fq_amount)
FROM loan_amount WHERE DATE_FORMAT(create_time,’%Y-%m-%d’)=DATE_FORMAT(NOW(),’%Y-%m-%d’)
GROUP BY DATE_FORMAT(create_time,’%Y-%m-%d %H’);

2. DATE_ADD() / DATE_SUB()

DATE_ADD(date,INTERVAL expr unit) 在一个时间上加一段时间得到另一个时间
DATE_SUB(date,INTERVAL expr unit) 在一个时间上减一段时间得到另一个时间

SELECT DATE_ADD(date,INTERVAL expr unit);#语法结构
#date 参数是合法的日期表达式
#expr 参数是时间间隔
espr参数可以如下表：

参数	参数含义
MICROSECOND	微秒
SECOND	秒
MINUTE	分
HOUR	时
DAY	天
WEEK	周
MONTH	月
QUARTER	季
YEAR	年
SECOND_MICROSECOND	秒微秒
MINUTE_MICROSECOND	分秒微秒
MINUTE_SECOND	分秒
HOUR_MICROSECOND	时分秒微秒
HOUR_SECOND	时分秒
HOUR_MINUTE	时分
DAY_MICROSECOND	天时分秒微秒
DAY_SECOND	天时分秒
DAY_MINUTE	天时分
DAY_HOUR	天时
YEAR_MONTH	年月

例1(基本用法）：获取明天日期
SELECT DATE_ADD(NOW(),INTERVAL ‘1’ DAY);
获取一年两个月之后日期
SELECT DATE_ADD(NOW(),INTERVAL ‘1,2’ YEAR_MONTH );
获取昨天日期
SELECT DATE_SUB(NOW(),INTERVAL ‘1’ DAY);
其实DATE_ADD(NOW(),INTERVAL -1 DAY)与DATE_SUB(NOW(),INTERVAL 1 DAY)结果一致。
例2：最近一周，一月有多少个，数据库的数据格式：2019-08-27
SELECT
count(DISTINCT(cdhd_usr_id)) AS 目标用户数,
count(DISTINCT(p__device_id)) AS 目标设备数
FROM
push_platform_upd
WHERE
last_dt >= DATE_SUB(
DATE_FORMAT(NOW(), ‘%Y-%m-%d’),
INTERVAL 30 DAY
)
例:3：求类似最近三十天金额总和
SELECT
sum(money)
FROM
push_platform_upd
WHERE
last_dt >= DATE_SUB(
DATE_FORMAT(NOW(), ‘%Y-%m-%d’),
INTERVAL 30 DAY
)

3.DATEDIFF（）

DATEDIFF 获取两个日期之间的天数
SELECT DATEDIFF(expr1,expr2); 得（expr1-expr2）的天数
#expr1 expr2 日期格式参数（‘’2020-09-26’不识别时分秒）

4.NIX_TIMESTAMP()

NIX_TIMESTAMP 转换成时间戳

5.FROM_UNIXTIME()

SELECT FROM_UNIXTIME(unix_timestamp,format);#语法结构
#unix_timestamp Unix时间戳
#format 格式化输出参数（格式参考date_format）

5.STR_TO_DATE()

相当于date_format的逆过程
把非标准日期转成标准日期
例如:SELECT STR_TO_DATE(‘Thu Sep 10 10:11:11 CST 2020’,’%a %b %d %H:%i:%S CST %Y’)
结果为 2020－09－10 10:11:11