日期类型

date&timestamp

select sysdate-to_date('2019/03/17','yyyy/mm/dd') from dual;  --number
select sysdate-to_timestamp('2019/03/17','yyyy/mm/dd') from dual; --interval
select systimestamp-1 from dual;  --date

interval类型

select interval '2' year  as "year",
       interval '10' month as "month",
       interval '3-9' year to month as "yeat to month",
       to_yminterval('5-2') as ym,
       interval '10' day as "day",
       interval '30' hour as "hour",
       interval '62' minute as "minute",
       interval '3.12' second as "second",
       interval '10 11:59:12' day to second as "day to second" ,
       to_dsinterval('10 02:03:04.3312') as ds,
       sysdate+numtodsinterval(3,'hour') as res from dual;

提取

select sysdate,to_number(to_char(sysdate,'hh24')) 时,
               to_number(to_char(sysdate,'mi'))   分,
               to_number(to_char(sysdate,'ss'))   秒,
               to_number(to_char(sysdate,'dd'))   日,
               to_number(to_char(sysdate,'mm'))   月,
               to_number(to_char(sysdate,'yyyy')) 年,
               to_number(to_char(sysdate,'ddd'))  第几天,
               trunc(sysdate,'dd')  天初, --trunc(sysdate)
               trunc(sysdate,'day') 周初,
               trunc(sysdate,'mm') 月初,
               last_day(sysdate)    月末,
               next_day(sysdate,4)  下一个周三，
               add_months(trunc(sysdate,'mm'),1) 下月初,
               trunc(sysdate,'yy') 年初,
               to_char(sysdate,'IW') 几周,
               to_char(sysdate,'day') 周几,        --to_char(sysdate,'d') 数值的周几;to_char(sysdate,'dy')
               to_char(sysdate,'month') 月份 from dual;

extract可从interval类型中提取

select extract( day    from dt2-dt1 ) day,
         extract( hour   from dt2-dt1 ) hour,
         extract( minute from dt2-dt1 ) minute,
         extract( second from dt2-dt1 ) second
    from (select to_timestamp('29-02-2000 01:02:03.122000',
                              'dd-mm-yyyy hh24:mi:ss.ff') dt1,
                 to_timestamp('15-05-2001 11:22:33.000000',
                              'dd-mm-yyyy hh24:mi:ss.ff') dt2
            from dual )；
select extract (year from sysdate),
       extract (month from sysdate) from dual;

统计全年周几出现次数

with x0 as
(select trunc(sysdate,'yy') as year_begin from dual),
x1 as
(select year_begin,add_months(year_begin,12) as next_year_begin from x0),
x2 as
(select year_begin,next_year_begin,next_year_begin-year_begin as days from x1),
x3 as
(select year_begin-1+level as day ,to_char(year_begin-1+level,'d') 周 from x2 connect by level <=days)
select count(周),周 from x3 group by 周　order by 2;

年日历

with dd as (select trunc(sysdate,'yy')+level-1  be from dual connect by level <=(add_months(trunc(sysdate,'yy'),12)-trunc(sysdate,'yy'))),
     x as (select to_char(be,'mm') as month,trunc(be-1,'d') first_day,to_char(be,'dd') day,to_number(to_char(be,'d')) weekday from dd)
 select month,max(decode(weekday,2,day)) "Mo"
             ,max(decode(weekday,3,day)) "Tu"
             ,max(decode(weekday,4,day)) "We"
             ,max(decode(weekday,5,day)) "Th"
             ,max(decode(weekday,6,day)) "Fr"
             ,max(decode(weekday,7,day)) "Sa"
             ,max(decode(weekday,1,day)) "Su" from x  group by month,first_day order by month,first_day;