当月第一天
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`