Oracle日期处理——Extract()函数

最新推荐文章于 2025-07-14 14:09:00 发布

原创最新推荐文章于 2025-07-14 14:09:00 发布 · 3k 阅读

2 ·

CC 4.0 BY-SA版权

Database 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了在Oracle数据库中进行日期时间操作的方法，包括计算两个日期之间的差值、从日期和时间戳中提取特定部分（如年、月、日等）、以及如何在当前时间上增加或减少指定的时间间隔。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.2个日期相差的天数：
--相减得到相差的天数
          select to_date('2012-10-18 15:29:01', 'yyyy-mm-dd hh24:mi:ss') -
                 to_date('2012-08-09:18:04:02', 'yyyy-mm-dd hh24:mi:ss')
            from dual;

2.相隔的天数，小时，分钟，秒数：
select extract(day from ts2 - ts1) day,
       extract(hour from ts2 - ts1) hour,
       extract(minute from ts2 - ts1) minute,
       extract(second from ts2 - ts1) second
  from (select to_timestamp('2011-10-18 15:29:02', 'yyyy-mm-dd hh24:mi:ss') ts1,
               to_timestamp('2012-12-26 17:22:05', 'yyyy-mm-dd hh24:mi:ss') ts2
          from dual);

--从date中截取year,month,day
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
或者
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'hh24') from dual;
select to_char(sysdate,'mi') from dual;
select to_char(sysdate,'ss') from dual;

--从timestamp截取year,month,day,hour,minute,second
select extract(year from timestamp'2012-12-10 12:02:01') from dual;
select extract(month from timestamp'2012-12-10 12:02:01') from dual;
select extract(day from timestamp'2012-12-10 12:02:01') from dual;
select extract(hour from timestamp'2012-12-10 12:02:01') from dual;
select extract(minute from timestamp'2012-12-10 12:02:01') from dual;
select extract(second from timestamp'2012-12-10 12:02:01') from dual;


--在当前时间减去3年
select   sysdate,sysdate - interval '3' year  from  dual;
--在当前时间减去3月
select   sysdate,sysdate - interval '3' month  from  dual;
--在当前时间减去3天
select   sysdate,sysdate - interval '3' day  from  dual;
--在当前时间减去3小时
select   sysdate,sysdate - interval '3' hour  from  dual;
--在当前时间减去3分钟
select   sysdate,sysdate - interval '3' MINUTE  from  dual;
--在当前时间减去3秒
select   sysdate,sysdate - interval '3' second  from  dual;
--在当前时间减去15秒
select   sysdate,sysdate - 5*interval '3' second  from  dual;
--在当前时间+15秒
select   sysdate,sysdate + 5*interval '3' second  from  dual;
--获取某个月的天数
select to_char(last_day(sysdate),'dd') from dual;
--获取季度
select  to_char(sysdate,'Q')  from dual;