日月年连表查询语句

oracle数据库
日

SELECT TO_CHAR(TO_DATE('20210316','yyyymmdd') + (LEVEL -1) ,'yyyymmdd') time FROM dual CONNECT BY LEVEL <=  1

月

select to_Char(add_months(TO_DATE('202103','yyyymm') ,level-1),'yyyymm') time FROM DUAL  CONNECT BY LEVEL <=3

年

select '2021'+(level-1) time FROM DUAL CONNECT BY LEVEL <=5

mysql数据库
需要创建临时表 timedual
建表语句

CREATE TABLE `timedual` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;```

添加数据   id 1-10000000排序

十五分钟
```sql
SELECT DATE_ADD(a.DATA_TIME,INTERVAL ((a.RN-1) * 30) MINUTE) DATA_TIME
FROM(SELECT id  RN, DATE_FORMAT('2021-01-01 00:00:00','%Y-%m-%d %H:%i:%s') DATA_TIME FROM timedual ORDER BY id LIMIT 0,100) as a```

日

```sql
SELECT DATE_ADD(a.DATA_TIME,INTERVAL ((a.RN-1) ) DAY) DATA_TIME
FROM(SELECT id  RN, DATE_FORMAT('2021-01-01','%Y-%m-%d') DATA_TIME FROM timedual ORDER BY id LIMIT 0,100) as a

月

SELECT   DATE_FORMAT(DATE_ADD( a.DATA_TIME, INTERVAL (( a.RN -1)  ) month ),'%Y%m') DATA_TIME 
FROM
	( SELECT id RN, DATE_FORMAT( '2021-03-01','%Y-%m-%d' ) DATA_TIME FROM timedual ORDER BY id LIMIT 0, 13 ) AS a

年

	SELECT DATE_FORMAT(DATE_ADD(a.DATA_TIME,INTERVAL ((a.RN-1) ) year ),'%Y') DATA_TIME
FROM(SELECT id  RN, DATE_FORMAT('2021-01-01','%Y-%m-%d') DATA_TIME FROM timedual ORDER BY id LIMIT 0,10) as a