常用使用场景: 统计某个月(某个时间区间)内每一天的数据量
-
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'))
