1、获取某时间开始24小时每小时列表数据
SELECT TO_DATE('2021-05-06','YYYY-MM-DD HH24:MI:SS')-(LEVEL-1.0)/24 AS DAT
FROM DUAL CONNECT BY LEVEL <> 24
2、获取某时间开始,往后推若干天时间列表
SELECT level,TO_DATE('2021-05-06','YYYY-MM-DD')- LEVEL + 1 AS DAT
FROM DUAL CONNECT BY LEVEL <= DATEDIFF(day,'2021-05-01','2021-05-06') + 1
3、当前时间往前往前推7天时间列表
SELECT level,TO_DATE(now(),'YYYY-MM-DD HH24:MI:SS')- LEVEL + 1 AS DAT
FROM DUAL CONNECT BY LEVEL <= 7
4、当前时间往后推7天时间列表
SELECT level,TO_DATE(now(),'YYYY-MM-DD HH24:MI:SS')+ LEVEL -1 AS DAT
FROM DUAL CONNECT BY LEVEL <= 7
5、查询某时间段和某表数据笛卡尔积
select
b.dat,
a.*
from
(
select
to_date(CURDATE(), 'YYYY-MM-DD') - level + 1 as dat
from
dual connect by level <= 5 ---当前时间 往前5天
)
b cross
join
(
select AREA,area_name from T_AREA_INFO where PARENT_CODE = '620000'
)
a
当前时间往前推10个月
select level,DATEADD(month,-level,getdate())
from
DUAL CONNECT BY LEVEL <= 10