mysql技巧

当月第一天

 SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)

当月最后一天

SELECT LAST_DAY(CURDATE())

上月第一天

SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL EXTRACT(DAY FROM NOW())-1 DAY),INTERVAL 1 MONTH)

上月最后一天

 SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY)

使用在字符传中

SELECT * FROM name WHERE  FIND_IN_SET(id, '1,4,8')

正常使用

SELECT * FROM name WHERE id IN(1,4,8)

时间戳转换

SELECT FROM_UNIXTIME(1608649722955/1000,’%Y/%m/%d %H:%i:%S’)

生成本周日期列表

SELECT
	@s := @s + 1 AS `index`,
	DATE( DATE_SUB( CURRENT_DATE, INTERVAL WEEKDAY(curdate())-@s DAY ) ) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s :=-1   ) temp 
WHERE
	@s < 6 
ORDER BY
	`date` 

生成最近7天的日期

SELECT
	@s := @s + 1 AS `index`,
	DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s :=0   ) temp 
WHERE
	@s < 7 
ORDER BY
	`date` 	

生成24小时列表时间

SELECT
	@s := @s + 1 AS `index`,
	DATE_FORMAT( DATE_SUB( DATE_SUB( curdate( ), INTERVAL 1 DAY ), INTERVAL - @s HOUR ), '%Y-%m-%d %H' ) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s := - 1 ) temp 
WHERE
	@s < 23 
ORDER BY
	`date`