[MySQL]获取当月每一天

常用使用场景: 统计某个月(某个时间区间)内每一天的数据量 

select date_add(curdate(), interval(cast(help_topic_id as signed integer) - 1) day) day

from mysql.help_topic

where help_topic_id < day(last_day(curdate()))

order by help_topic_id

 

延伸用法: 获取一段时间内的每分钟

set @stime = str_to_date('2018-11-07 08:00', '%Y-%m-%d %H:%i');

set @etime = str_to_date('2018-11-07 08:10', '%Y-%m-%d %H:%i');

select date_add(@stime, interval (cast(help_topic_id as signed integer) - 0) minute) minute

from mysql.help_topic

where help_topic_id <= timestampdiff(minute, @stime, @etime)

order by help_topic_id

 

顺便贴一下oracle的写法

select trunc(sysdate, 'MM') + rownum - 1 day

from dual

connect by rownum <= to_number(to_char(last_day(sysdate), 'dd'))

原文地址：https://blog.csdn.net/reeye_/article/details/83655477

转载于:https://www.cnblogs.com/jpfss/p/11131335.html