获取两个时间段内的日
SELECT
date_format(`date`, '%Y-%m-%d') yearAndMonthAndDay
FROM
(
SELECT
adddate(
'1970-01-01'
, t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i
) `date`
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
) t0
, (
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
) t1
, (
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
) t2
, (
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
) t3
, (
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
) t4
) v
WHERE
`date` >= date_format('20210900', '%Y-%m-%d')
AND `date` <= date_format(now(), '%Y-%m-%d')
获取两个时间段内的周(某年的第几周)
SELECT
date_format(`date`, '%Y-%u') yearAndWeek
FROM
(
SELECT
adddate(
'1970-01-01'
, INTERVAL t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i WEEK
) `date`
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
) t0
, (
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
) t1
, (
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
) t2
, (
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
) t3
) a
WHERE
`date` >= date_format('20210901', '%Y-%m-%d')
AND `date` <= date_format(now(), '%Y-%m-%d')
获取两个时间段内的月
SELECT
date_format(`date`, '%Y-%m') yearAndMonth
FROM
(
SELECT
adddate(
'1970-01-01'
, INTERVAL t2.i * 100 + t1.i * 10 + t0.i MONTH
) `date`
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
) t0
, (
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
) t1
, (
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
) t2
) a
WHERE
`date` >= date_format('20210900', '%Y-%m-%d')
AND `date` <= date_format(now(), '%Y-%m-%d')