MySql时间计算

SELECT 
TIME_TO_SEC(TIMEDIFF('2018-09-30 19:38:45', '2018-08-23 10:13:01')) AS DIFF_SECOND1, -- 秒
UNIX_TIMESTAMP('2018-09-30 19:38:45')-UNIX_TIMESTAMP('2018-08-23 10:13:01') AS DIFF_SECOND2, -- 秒
TIMESTAMPDIFF(SECOND,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_SECOND3, -- 秒
TIMESTAMPDIFF(MINUTE,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_MINUTE, -- 分
TIMESTAMPDIFF(HOUR,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_HOUR, -- 小时
TIMESTAMPDIFF(DAY ,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_DATE1, -- 天
DATEDIFF('2018-09-30 19:38:45','2018-08-23 10:13:01') AS DIFF_DATE2, -- 天
TIMESTAMPDIFF(MONTH,'2018-08-23 10:13:01','2018-09-25 19:38:45') AS DIFF_MONTH, -- 月
TIMESTAMPDIFF(YEAR,'2018-08-23 10:13:01','2020-07-25 19:38:45') AS DIFF_YEAR -- 年
FROM DUAL;

生成最近七天的日期不包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date FROM(SELECT @cdate := date_add(date_sub(CURDATE(),interval 1 DAY), interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-16
2020-06-15
2020-06-14
2020-06-13
2020-06-12
2020-06-11
2020-06-10

生成最近七天的日期包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date_str FROM(SELECT @cdate := date_add(CURDATE(), interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-17
2020-06-16
2020-06-15
2020-06-14
2020-06-13
2020-06-12
2020-06-11

生成最近七天的日期包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date_str FROM(SELECT @cdate := date_add('202006017', interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-17
2020-06-16
2020-06-15
2020-06-14
2020-06-13
2020-06-12
2020-06-11

生成十二天每月的日期包括当月

SELECT @cdate := date_add(@cdate, interval - 1 MONTH) as date_str FROM(SELECT @cdate := date_add(CURDATE(), interval + 1 MONTH) from resource_publish) t0 LIMIT 12;

2020-06-17
2020-05-17
2020-04-17
2020-03-17
2020-02-17
2020-01-17
2019-12-17
2019-11-17
2019-10-17
2019-09-17
2019-08-17
2019-07-17

查询最近七个月每月日期

SELECT @cdate := date_add(@cdate, interval - 1 MONTH) as daytime FROM(SELECT @cdate := date_add(date_sub(CURDATE(),interval 1 MONTH), interval + 1 MONTH) from test_tb) t0 LIMIT 7;

2020-05-17
2020-04-17
2020-03-17
2020-02-17
2020-01-17
2019-12-17
2019-11-17

查询最近六个月每月月末日期

SELECT @lastDay := last_day( date_add(@lastDay,interval 1 month )) lastDays from (SELECT @lastDay := date_add(curdate(),interval -6 month) from test_tb limit 6) a;

2020-01-31
2020-02-29
2020-03-31
2020-04-30
2020-05-31
2020-06-30

查询最近七月的月份

SELECT date_format(@lastDay := last_day( date_add(@lastDay,interval 1 month ) ) ,'%Y-%m' ) lastDays from (SELECT @lastDay := date_add(curdate(),interval -6 month) from test_tb limit 6) a;

2020-01
2020-02
2020-03
2020-04
2020-05
2020-06

获取当年所有月份

1	SELECT date_format(@lastDay := last_day(date_add(@lastDay,interval 1 month)) ,'%Y-%m') lastDays from (SELECT @lastDay := date_add(curdate(),interval -MONTH(curdate()) month) from test_tb LIMIT 12) a;

获取当年所有月份

SELECT CASE WHEN length(mon) = 1 THEN concat(LEFT (CURRENT_DATE, 5),'0',mon)  ELSE concat(LEFT(CURRENT_DATE, 5), mon) END months FROM (SELECT @m :=@m + 1 mon FROM test_tb,(SELECT @m := 0) a) aa LIMIT 12;

2020-01
2020-02
2020-03
2020-04
2020-05
2020-06
2020-07
2020-08
2020-09
2020-10
2020-11
2020-12

生成当年十二个月

SELECT CONCAT(YEAR(CURRENT_DATE()),'-01') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-02') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-03') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-04') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-05') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-06') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-07') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-08') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-09') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-10') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-11') AS date UNION
SELECT CONCAT(YEAR(CURRENT_DATE()),'-12') AS date

获取当年第一个月份

1
2
3

SELECT date_format(date_sub(date_sub(curdate(),interval day(curdate()) - 1 day),interval MONTH(CURDATE())-1 month),'%Y-%m');

2020-01

上月月份

1
2
3

select extract(YEAR_MONTH from date_add(NOW(), interval -1 month));

202005

上年月份

1
2
3

select extract(YEAR_MONTH from date_add(NOW(), interval -1 year));

201906

查看最近两周内的工作日

SELECT
	* 
FROM
	(
	SELECT
		daystr,
		WEEKDAY( daystr ) AS wd 
	FROM
		(
		SELECT
			@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS daystr 
		FROM
			( SELECT @cdate := date_add( date_sub( CURDATE(), INTERVAL 1 DAY ), INTERVAL + 1 DAY ) FROM tb_name ) t0 
			LIMIT 14 
		) ds 
	) aa 
WHERE
	wd != '5' 
	AND wd != '6';


1    2020-06-16
0    2020-06-15
4    2020-06-12
3    2020-06-11
2    2020-06-10
1    2020-06-09
0    2020-06-08
4    2020-06-05
3    2020-06-04
2    2020-06-03

和其他列组合自动生成序号

1 2	set @rownum=0; select @rownum:=@rownum+1 as rownum;select (@i:=@i+1) as rownum FROM (select @i:=0) as rn;

获取六周前的周一

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 35 DAY);

2020-05-11

获取当前周所在的周一

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 0 DAY);

2020-06-15

获取当前周所在的周日

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 6 DAY);

2021-07-18

获取六个月前第一天

1
2
3

SELECT date_add(DATE_ADD(curdate(),interval -day(curdate())+1 day),interval -6 month);

2021-01-01

获取当月第一天

1
2
3

SELECT DATE_ADD(curdate(),interval -day(curdate())+1 day);

2020-06-01

当前日期是第几周

-- YEARWEEK 是获取年份和周数的一个函数，函数形式为 YEARWEEK(date[,mode])
-- WEEKOFYEAR(date) 返回日期用数字表示的范围是从1到53的日历周。WEEKOFYEAR()是一个兼容性函数，它等效于WEEK(date,3)。
-- WEEK(date[,mode]) 此函数返回日期的周数。双参数的形式WEEK()允许你指定星期是否开始于周日或周一，以及是否返回值应在范围从0到53或从1到53。 如果省略了mode参数，系统default_week_format变量的值被使用。
SELECT YEARWEEK('2020-06-17');
202024

SELECT WEEKOFYEAR('2020-06-17');
25

SELECT WEEK(‘2020-06-17’,1);
25

当前日期在当月第几周

1 2	-- DAYOFWEEK 转换日期为周几的函数,1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday select DAYOFWEEK('2020-06-17');

查看当日是周几

1	SELECT dayofweek(now());

当年第一天

1
2
3

select date_sub(curdate(),interval dayofyear(now())-1 day);

2020-01-01

当年第一天

-- date_format(date，'%u')
-- %U 周 (01-53) 星期日是一周的第一天
-- %u 周 (01-53) 星期一是一周的第一天

select concat(date_format(last_day(makedate(extract(year from curdate()),1) + interval quarter(curdate())*3-6 month),'%y-%m-'),'01'); 
　　
2020-01-01

当年最后一天

1
2
3

SELECT concat(YEAR(now()),'-12-31');
　　
2020-12-31

当前week的第一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);

2020-06-14

当前week的最后一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);

2020-06-20

前一week的第一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY);

2020-06-07

前一week的最后一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY);
　　
2020-06-13

前两week的第一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY);

2020-05-31

前两week的最后一天

1
2
3

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY);

2020-06-06

获取当前日期

1	SELECT NOW(),CURDATE(),CURTIME()

获取前一天日期

1	select date_sub(curdate(),interval 1 day);

获取后一天

1	SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);

获取当月最后一天

1
2
3

select last_day(curdate());

2020-06-30

当前月的最后一天

1
2
3

select last_day(now());

2020-06-30

当前月的最后一天

1
2
3

select last_day(makedate(extract(year from curdate()),1) + interval quarter(curdate())*3-1 month); 

2020-06-30

获取本月第一天

1
2
3

select DATE_ADD(curdate(),interval -day(curdate())+1 day);

2020-06-01

获取本月第一天

1
2
3

select date_add(curdate(), interval - day(curdate()) + 1 day);

2020-06-01

当前月的第一天

1
2
3

select concat(date_format(last_day(now()),'%y-%m-'),'01');

2020-06-01

获取上月月份

1
2
3

select extract(YEAR_MONTH from date_add(NOW(), interval -1 month));

202005

上月第一天

select date_sub(date_sub(date_format(now(),'%Y-%m-%d'),interval extract(day from now())-1 day),interval 1 month);
SELECT date_sub(date_sub(curdate(),interval day(curdate()) - 1 day),interval 1 month);

2020-05-01

上月第一天

1
2
3

select concat(date_format(last_day(now() - interval 1 month),'%y-%m-'),'01');

2020-05-01

获取上月月份

SELECT date_format(date_sub(date_sub(curdate(),interval day(curdate()) - 1 day),interval 1 month),'%Y-%m');
select date_format(date_add(curdate(),interval -1 month),'%Y-%m');

2020-05

获取当年1月份

1
2
3

select date_format(date_sub(date_sub(curdate(),interval day(curdate()) - 1 day),interval month(curdate())-1 month),'%Y-%m');

2020-01

获取下个月的第一天

1
2
3

select date_add(curdate()-day(curdate())+1,interval 1 month); 

2020-07-01

获取当前月的天数

1
2
3

select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual;

30

上两月第一天

1
2
3

select concat(date_format(last_day(now() - interval 2 month),'%y-%m-'),'01'); 

2020-04-01

上两月第一天

1
2
3

select concat(date_format(last_day(makedate(extract(year from curdate()),1) + interval quarter(curdate())*3-3 month),'%Y-%m-'),'01');
　　
2020-04-01

上月最后一天

1
2
3

select last_day(now() - interval 1 month); 

2020-05-31

上两月最后一天

1
2
3

select last_day(now() - interval 2 month);

2020-04-30

上两月最后一天

1
2
3

select last_day(makedate(extract(year from curdate()),1) + interval quarter(curdate())*3-3 month); 

2020-04-30

上两月最后一天

1
2
3

select last_day(makedate(extract(year from curdate()),1) + interval quarter(curdate())*3-3 month);

2020-04-30

MySQL 查询百分占比三种方式

-- 1.字段里嵌套子查询统计总数
-- 2.嵌套临时表统计总数
-- 3.设置临时变量统计总数

set @sum=(select sum(ct_col) from test_tb);
select ct_col,round((ct_col/@sum*100),2) as ct from test_tb group by ct_col order by ct desc;

MySQL时间格式化

1
2
3

SELECT DATE_FORMAT('2020-06-17 15:23:16.463','%Y-%m-%d %H:%i:%s.%f')

2020-06-17 15:23:16.463000

MySQL判空函数

1
2
3

IFNULL(expr,expr) 　　　　　 -- 处理如果字段为空，返回默认值，sum是统计求和，
IFNULL(SUM(expr1),expr2)　　-- 是统计求和之后为空返回默认值
SUM(IFNULL(expr1,expr2))　　-- 是判断字段为空返回默认值再求和

MySQL两时间计算、年份差、月份差、天数差

-- 年份差
SELECT TIMESTAMPDIFF(YEAR,'2017-05-01', DATE_FORMAT(now(), '%Y-%m-%d'))
-- 月份差
SELECT TIMESTAMPDIFF(MONTH,'2020-01-01', '2021-01-01')
-- 天数差
SELECT datediff(DATE_FORMAT(now(), '%Y-%m-%d'),DATE_FORMAT('2018-09-10','%Y-%m-%d'))
SELECT TIMESTAMPDIFF(DAY,'2017-05-01', DATE_FORMAT(now(), '%Y-%m-%d'))

根据时间差生成月份列表

-- 今后的日期可达近80年
SELECT DATE_FORMAT(DATE_SUB('2021-01-01', INTERVAL d MONTH), '%Y-%m') as year_month_day
FROM (
select d from (
SELECT @xi:=@xi+1 as d from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc3,
(SELECT @xi:=-1) xc0
) as m where d <= TIMESTAMPDIFF(MONTH,'2020-01-01', '2021-01-01')
) dtc

根据时间差生成日期列表

-- 今后的日期可达近300年
SELECT DATE_FORMAT(DATE_SUB('2021-01-01', INTERVAL d DAY), '%Y-%m-%d') as year_month_day
FROM (
select d from (
SELECT @xi:=@xi+1 as d from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc4,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) xc5,
(SELECT @xi:=-1) xc0
) as m where d <= TIMESTAMPDIFF(DAY,'2020-01-01', '2021-01-01')
) dtc