oracle获取最近24小时记录，同时获取小时字段

进击小螺号

已于 2024-02-04 16:39:22 修改

阅读量773

点赞数 11

文章标签： sql 数据库 oracle

于 2024-02-04 15:48:22 首次发布

本文链接：https://blog.csdn.net/Crazysuperlittleboy/article/details/136027383

版权

--获取最近24小时记录，同时获取小时字段

SELECT (SYSDATE - 1) + (ROWNUM - 1) / 24 DATE_TIME,
TO_DATE(TO_CHAR((SYSDATE - 1) + (ROWNUM - 1) / 24, 'YYYY-MM-DD'), 'YYYY-MM-DD') GCRQ,
TO_NUMBER(TO_CHAR((SYSDATE - 1) + (ROWNUM - 1) / 24, 'HH24')) HOURS
FROM DUAL CONNECT BY ROWNUM <= 24

获取一下结果

-- 获取某天每小时的时间
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1) / 24
FROM DUAL CONNECT BY ROWNUM <= 24;

-- 获取某天的每分钟时间
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1) / (24 * 60)
FROM DUAL CONNECT BY ROWNUM <= 24*60;

-- 获取某天的每10分钟时间
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1) / (24 * 6)
FROM DUAL CONNECT BY ROWNUM <= 24*6;

-- 获取某时间段内每天的日期
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1)
FROM DUAL CONNECT BY ROWNUM <=
TRUNC(TO_DATE('2020-03-23', 'YYYY-MM-DD')) - TRUNC(TO_DATE('2018-03-23', 'YYYY-MM-DD'));

-- 获取某时间段内每周的起始日期和其周数
SELECT TRUNC(TO_DATE('2018-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7， 'D') + 1 AS STARTTIME,
TO_CHAR(TO_DATE('2018-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7， 'IW') AS WEEK
FROM DUAL CONNECT BY ROWNUM <=
TRUNC(TO_DATE('2019-03-02', 'YYYY-MM-DD') - TO_DATE('2018-03-02', 'YYYY-MM-DD')) / 7 + 1;

-- 获取某时间段内的每月
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2018-03-02', 'YYYY-MM-DD'), ROWNUM - 1), 'YYYY-MM')
FROM DUAL CONNECT BY ROWNUM <=
CEIL(MONTHS_BETWEEN(TO_DATE('2020-03-03', 'YYYY-MM-DD'), TO_DATE('2018-03-02','YYYY-MM-DD')));

-- 获取某时间段内每年的年份
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2018-03-02', 'YYYY-MM-DD'), (ROWNUM - 1) * 12), 'YYYY')
FROM DUAL CONNECT BY ROWNUM <=
CEIL(MONTHS_BETWEEN(TO_DATE('2020-03-03', 'YYYY-MM-DD'), TO_DATE('2018-03-02','YYYY-MM-DD')) / 12);