mysql dateadd格式化_MYSQL中对时间的处理函数(DATE_FORMAT(),DATE_ADD() / DATE_SUB(),DATEDIFF（）,NIX_TIMESTAMP(),FRO...

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)