Oracle按时间范围查询sql分享
查询时间范围内对应的年
SELECT
TO_CHAR (
ADD_MONTHS (
TO_DATE ('2013', 'yyyy'),
(ROWNUM - 1) * 12
),
'yyyy'
) AS years
FROM
DUAL CONNECT BY ROWNUM <= MONTHS_BETWEEN (
TO_DATE ('2018', 'yyyy'),
TO_DATE ('2013', 'yyyy')
) / 12 + 1
查询时间范围内对应的月份
SELECT
TO_CHAR (
ADD_MONTHS (
TO_DATE ('2017-10', 'yyyy-MM'),
ROWNUM - 1
),
'yyyy-MM'
) AS months
FROM
DUAL CONNECT BY ROWNUM <= MONTHS_BETWEEN (
TO_DATE ('2018-04', 'yyyy-MM'),
TO_DATE ('2017-10', 'yyyy-MM')
) + 1
查询时间范围内对应的日期
SELECT
TO_CHAR (
TO_DATE ('2017-11-01', 'yyyy-MM-dd') + ROWNUM - 1,
'yyyy-MM-dd'
) AS TIME
FROM
DUAL CONNECT BY ROWNUM <= TRUNC (
TO_DATE ('2017-12-01', 'yyyy-MM-dd') - TO_DATE ('2017-11-01', 'yyyy-MM-dd')
) + 1
查询一天的24 个小时
SELECT
TO_CHAR (
TO_DATE ('2017-01-01', 'yyyy-mm-dd') + (ROWNUM - 1) / 24,
'hh24'
) AS HOUR
FROM
dual CONNECT BY 24 >= ROWNUM
当然 以上的sql语句都是基于Oracle数据库的 ，对于其他的数据库并不是很合适。