Oracle按时间范围查询sql分享 oracle按照时间查询

查询时间范围内对应的年

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数据库的 ，对于其他的数据库并不是很合适。