oracle 获取时间

小咪咪 2021-11-10 03:02 454阅读 0赞

Oracle关于时间/日期的操作

1.日期时间间隔操作

当前时间减去7分钟的时间  
select  sysdate,sysdate - interval '7' MINUTE  from dual  
当前时间减去7小时的时间  
select  sysdate - interval '7' hour  from dual  
当前时间减去7天的时间  
select  sysdate - interval '7' day  from dual  
当前时间减去7月的时间  
select  sysdate,sysdate - interval '7' month from dual  
当前时间减去7年的时间  
select  sysdate,sysdate - interval '7' year   from dual  
时间间隔乘以一个数字  
select  sysdate,sysdate - 8 \*interval '2' hour   from dual

2.日期到字符操作   
select  sysdate,to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual  
select  sysdate,to\_char(sysdate,'yyyy-mm-dd hh:mi:ss')  from dual  
select  sysdate,to\_char(sysdate,'yyyy-ddd hh:mi:ss')  from dual  
select  sysdate,to\_char(sysdate,'yyyy-mm iw-d hh:mi:ss')  from dual  
   参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL\_ELEMENTS4.HTM\#48515)

3. 字符到日期操作  
select  to\_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual  
具体用法和上面的to\_char差不多。

4. trunk/ ROUND函数的使用   
select  trunc(sysdate ,'YEAR')  from dual  
select  trunc(sysdate )  from dual  
select  to\_char(trunc(sysdate ,'YYYY'),'YYYY')  from  dual

5.oracle有毫秒级的数据类型  
\--返回当前时间 年月日小时分秒毫秒  
select to\_char(current\_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;   
\--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)  
select to\_char(current\_timestamp(9),'MI:SSxFF') from dual;

6.计算程序运行的时间(ms)   
declare  
    type rc is ref cursor;  
    l\_rc rc;  
    l\_dummy all\_objects.object\_name%type;  
    l\_start number default dbms\_utility.get\_time;  
begin  
    for  I  in 1 .. 1000  
    loop  
        open l\_rc for  
          'select object\_name  from all\_objects '||

'where object\_id = ' || i;  
        fetch l\_rc into l\_dummy;  
        close l\_rc;  
    end loop;  
    dbms\_output.put\_line  
    ( round( (dbms\_utility.get\_time-l\_start)/100, 2 ) ||  
      ' seconds...' );  
end;

问题的提出：  
如果一个表在一个date类型的字段上面建立了索引，如何使用

alter session set NLS\_DATE\_FORMAT='YYYY-MM-DD HH24:MI:SS'

1: 得到当前的日期  
select sysdate from dual;

2: 得到当天凌晨0点0分0秒的日期  
select trunc(sysdate) from dual;

\-- 得到这天的最后一秒  
select trunc(sysdate) + 0.99999 from dual;

\-- 得到小时的具体数值  
select trunc(sysdate) + 1/24 from dual;

select trunc(sysdate) + 7/24 from dual;

得到明天凌晨0点0分0秒的日期

select trunc(sysdate+1) from dual;

select trunc(sysdate)+1 from dual;

3: 本月一日的日期  
select trunc(sysdate,'mm') from dual;

得到下月一日的日期  
select trunc(add\_months(sysdate,1),'mm') from dual;

返回当前月的最后一天?  
select last\_day(sysdate)  from dual;          
select last\_day(trunc(sysdate))  from dual;    
select trunc(last\_day(sysdate))  from dual;  
select trunc(add\_months(sysdate,1),'mm') - 1 from dual;

4: 得到一年的每一天  
select trunc(sysdate,'yyyy')+ rn -1 date0   
from   
(select rownum rn from all\_objects   
where rownum<366);

今天是今年的第N天  
SELECT TO\_CHAR(SYSDATE,'DDD') FROM DUAL;

如何在给现有的日期加上2年  
select add\_months(sysdate,24) from dual;

判断某一日子所在年分是否为润年   
select decode(to\_char(last\_day(trunc(sysdate,'y')+31),'dd'),'29','闰年','平年') from dual;

判断两年后是否为润年  
select decode(to\_char(last\_day(trunc(add\_months(sysdate,24),'y')+31),'dd'),'29','闰年','平年') from dual;

5:  
得到日期的季度

select ceil(to\_number(to\_char(sysdate,'mm'))/3) from dual;

select to\_char(sysdate, 'Q') from dual;

在oracle中处理日期大全      
   
   TO\_DATE格式        
Day:        
dd  number  12        
dy  abbreviated  fri        
day  spelled  out  friday        
ddspth  spelled  out,  ordinal  twelfth        
Month:        
mm  number  03        
mon  abbreviated  mar        
month  spelled  out  march        
Year:        
yy  two  digits  98        
yyyy  four  digits  1998        
   
24小时格式下时间范围为：  0:00:00  -  23:59:59....        
12小时格式下时间范围为：  1:00:00  -  12:59:59  ....        
1.        
日期和字符转换函数用法（to\_date,to\_char）        
   
2.        
select  to\_char(  to\_date(222,'J'),'Jsp')  from  dual        
   
显示Two  Hundred  Twenty-Two        
   
3.        
求某天是星期几        
select  to\_char(to\_date('2002-08-26','yyyy-mm-dd'),'day')  from  dual;        
星期一        
select  to\_char(to\_date('2002-08-26','yyyy-mm-dd'),'day','NLS\_DATE\_LANGUAGE  =  American')  from  dual;        
monday        
设置日期语言        
ALTER  SESSION  SET  NLS\_DATE\_LANGUAGE='AMERICAN';        
也可以这样        
TO\_DATE  ('2002-08-26',  'YYYY-mm-dd',  'NLS\_DATE\_LANGUAGE  =  American')        
   
4.        
两个日期间的天数        
select  floor(sysdate  -  to\_date('20020405','yyyymmdd'))  from  dual;        
   
5.  时间为null的用法        
select  id,  active\_date  from  table1        
UNION        
select  1,  TO\_DATE(null)  from  dual;        
   
注意要用TO\_DATE(null)        
   
6.        
a\_date  between  to\_date('20011201','yyyymmdd')  and  to\_date('20011231','yyyymmdd')        
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。        
所以，当时间需要精确的时候，觉得to\_char还是必要的        
7.  日期格式冲突问题        
输入的格式要看你安装的ORACLE字符集的类型,  比如:  US7ASCII,  date格式的类型就是:  '01-Jan-01'        
alter  system  set  NLS\_DATE\_LANGUAGE  =  American        
alter  session  set  NLS\_DATE\_LANGUAGE  =  American        
或者在to\_date中写        
select  to\_char(to\_date('2002-08-26','yyyy-mm-dd'),'day','NLS\_DATE\_LANGUAGE  =  American')  from  dual;        
注意我这只是举了NLS\_DATE\_LANGUAGE，当然还有很多，        
可查看        
select  \*  from  nls\_session\_parameters        
select  \*  from  V$NLS\_PARAMETERS        
   
8.        
select  count(\*)        
from  (  select  rownum-1  rnum        
from  all\_objects        
where  rownum  <=  to\_date('2002-02-28','yyyy-mm-dd')  -  to\_date('2002-        
02-01','yyyy-mm-dd')+1        
)        
where  to\_char(  to\_date('2002-02-01','yyyy-mm-dd')+rnum-1,  'D'  )        
not        
in  (  '1',  '7'  )        
   
查找2002-02-28至2002-02-01间除星期一和七的天数        
在前后分别调用DBMS\_UTILITY.GET\_TIME,  让后将结果相减(得到的是1/100秒,  而不是毫秒).        
   
9.        
select  months\_between(to\_date('01-31-1999','MM-DD-YYYY'),        
to\_date('12-31-1998','MM-DD-YYYY'))  "MONTHS"  FROM  DUAL;        
1        
   
select  months\_between(to\_date('02-01-1999','MM-DD-YYYY'),        
to\_date('12-31-1998','MM-DD-YYYY'))  "MONTHS"  FROM  DUAL;        
   
1.03225806451613        
10.  Next\_day的用法        
Next\_day(date,  day)        
   
Monday-Sunday,  for  format  code  DAY        
Mon-Sun,  for  format  code  DY        
1-7,  for  format  code  D        
   
11        
select  to\_char(sysdate,'hh:mi:ss')  TIME  from  all\_objects        
注意：第一条记录的TIME  与最后一行是一样的        
可以建立一个函数来处理这个问题        
create  or  replace  function  sys\_date  return  date  is        
begin        
return  sysdate;        
end;        
   
select  to\_char(sys\_date,'hh:mi:ss')  from  all\_objects;        
12.        
获得小时数        
   
SELECT  EXTRACT(HOUR  FROM  TIMESTAMP  '2001-02-16  2:38:40')  from  offer        
SQL>  select  sysdate  ,to\_char(sysdate,'hh')  from  dual;        
   
SYSDATE  TO\_CHAR(SYSDATE,'HH')        
\--------------------  ---------------------        
2003-10-13  19:35:21  07        
   
SQL>  select  sysdate  ,to\_char(sysdate,'hh24')  from  dual;        
   
SYSDATE  TO\_CHAR(SYSDATE,'HH24')        
\--------------------  -----------------------        
2003-10-13  19:35:21  19        
   
获取年月日与此类似        
13.        
年月日的处理        
select  older\_date,        
newer\_date,        
years,        
months,        
abs(        
trunc(        
newer\_date-        
add\_months(  older\_date,years\*12+months  )        
)        
)  days        
from  (  select        
trunc(months\_between(  newer\_date,  older\_date  )/12)  YEARS,        
mod(trunc(months\_between(  newer\_date,  older\_date  )),        
12  )  MONTHS,        
newer\_date,        
older\_date        
from  (  select  hiredate  older\_date,        
add\_months(hiredate,rownum)+rownum  newer\_date        
from  emp  )        
)        
   
14.        
处理月份天数不定的办法        
select  to\_char(add\_months(last\_day(sysdate)  +1,  -2),  'yyyymmdd'),last\_day(sysdate)  from  dual        
   
16.        
找出今年的天数        
select  add\_months(trunc(sysdate,'year'),  12)  -  trunc(sysdate,'year')  from  dual        
   
闰年的处理方法        
to\_char(  last\_day(  to\_date('02'    |  |  :year,'mmyyyy')  ),  'dd'  )        
如果是28就不是闰年        
   
17.        
yyyy与rrrr的区别        
'YYYY99  TO\_C        
\-------  ----        
yyyy  99  0099        
rrrr  99  1999        
yyyy  01  0001        
rrrr  01  2001        
   
18.不同时区的处理        
select  to\_char(  NEW\_TIME(  sysdate,  'GMT','EST'),  'dd/mm/yyyy  hh:mi:ss')  ,sysdate        
from  dual;        
   
19.        
5秒钟一个间隔        
Select  TO\_DATE(FLOOR(TO\_CHAR(sysdate,'SSSSS')/300)  \*  300,'SSSSS')  ,TO\_CHAR(sysdate,'SSSSS')        
from  dual        
   
2002-11-1  9:55:00  35786        
SSSSS表示5位秒数

关于10g的date类型：  
10g:的日期格式：的dd-mon-yyyy.  
但月的输入必须用日语比如：2004/10/20  
应该输入：20-10月-2005  
呵呵，很变态。  
关于语言的设置，是环境变量的问题：  
需要调整NLS\_DATE\_LANGUAGE, NLS\_DATE\_FORMAT  
例如，你需要的日期格式是这样的: 01-JAN-2004

alter session set NLS\_DATE\_LANGUAGE='AMERICAN'  
alter session set nls\_date\_format='DD-MON-YYYY HH24:MI:SS'

SQL> select sysdate from dual;

SYSDATE  
\----------  
05-7月 -04

SQL> alter session set NLS\_DATE\_LANGUAGE='AMERICAN';

会话已更改。

SQL> select sysdate from dual;

SYSDATE  
\---------  
05-JUL-04

20.        
一年的第几天        
select  TO\_CHAR(SYSDATE,'DDD'),sysdate  from  dual        
310  2002-11-6  10:03:51        
   
21.计算小时,分,秒,毫秒        
select        
Days,        
A,        
TRUNC(A\*24)  Hours,        
TRUNC(A\*24\*60  -  60\*TRUNC(A\*24))  Minutes,        
TRUNC(A\*24\*60\*60  -  60\*TRUNC(A\*24\*60))  Seconds,        
TRUNC(A\*24\*60\*60\*100  -  100\*TRUNC(A\*24\*60\*60))  mSeconds        
from        
(        
select        
trunc(sysdate)  Days,        
sysdate  -  trunc(sysdate)  A        
from  dual        
)        
   
   
   
select  \*  from  tabname        
order  by  decode(mode,'FIFO',1,-1)\*to\_char(rq,'yyyymmddhh24miss');        
   
//        
floor((date2-date1)  /365)  作为年        
floor((date2-date1,  365)  /30)  作为月        
mod(mod(date2-date1,  365),  30)作为日.        
23.next\_day函数        
next\_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。        
1  2  3  4  5  6  7        
日  一  二  三  四  五  六      
   
\---------------------------------------------------------------    
   
select    (sysdate-to\_date('2003-12-03  12:55:45','yyyy-mm-dd  hh24:mi:ss'))\*24\*60\*60  from  ddual  
日期  返回的是天  然后  转换为ss  
取上个月月份的SQL

select decode(to\_char(sysdate,'mm')-1,0,to\_char(sysdate,'yyyy')-1,to\_char(sysdate,'yyyy'))||case when to\_number(decode(to\_char(sysdate,'mm')-1,0,12,to\_char(sysdate,'mm')-1)) < 10 then '0'||decode(to\_char(sysdate,'mm')-1,0,12,to\_char(sysdate,'mm')-1) else ''||decode(to\_char(sysdate,'mm')-1,0,12,to\_char(sysdate,'mm')-1) end datev from dual  
程序开发: Oracle各种日期计算方法(收藏)

一个月的第一天   
SELECT to\_date(to\_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd') FROM dual  
sysdate 为数据库服务器的当前系统时间。  
to\_char 是将日期型转为字符型的函数。  
to\_date 是将字符型转为日期型的函数，一般使用 yyyy-mm-dd hh24:mi:ss格式，当没有指定时间部分时，则默认时间为 00:00:00

dual 表为sys用户的表，这个表仅有一条记录，可以用于计算一些表达式，如果有好事者用 sys 用户登录系统，然后在 dual 表增加了记录的话，那么系统99.999%不能使用了。为什么使用的时候不用 sys.dual 格式呢，因为 sys 已经为 dual 表建立了所有用户均可使用的别名。

一年的第一天   
SELECT to\_date(to\_char(SYSDATE,'yyyy')||'-01-01','yyyy-mm-dd') FROM dual  
季度的第一天   
SELECT to\_date(to\_char(SYSDATE,'yyyy-')||lpad(floor(to\_number(to\_char(SYSDATE,'mm'))/3)\*3+1,2,'0')||'-01','yyyy-mm-dd') FROM dual  
floor 为向下取整  
lpad 为向左使用指定的字符扩充字符串，这个扩充字符串至2位，不足的补'0'。

当天的半夜  
SELECT trunc(SYSDATE)+1-1/24/60/60  
FROM dual

trunc 是将 sysdate 的时间部分截掉，即时间部分变成 00:00:00  
Oracle中日期加减是按照天数进行的，所以 +1-1/24/60/60 使时间部分变成了 23:59:59。  
Oracle 8i 中仅支持时间到秒，9i以上则支持到 1/100000000 秒。

上个月的最后一天   
SELECT trunc(last\_day(add\_months(SYSDATE,-1)))+1-1/24/60/60 FROM dual  
add\_months 是月份加减函数。  
last\_day 是求该月份的最后一天的函数。

本年的最后一天   
SELECT trunc(last\_day(to\_date(to\_char(SYSDATE,'yyyy')||'-12-01','yyyy-mm-dd')))+1-1/24/60/60 FROM dual  
本月的最后一天   
select trunc(last\_day(sysdate))+1-1/24/60/60 from dual

本月的第一个星期一   
SELECT next\_day(to\_date(to\_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd'),'星期一') FROM dualnext\_day 为计算从指定日期开始的第一个符合要求的日期，这里的'星期一'将根据NLS\_DATE\_LANGUAGE的设置稍有不同。

去掉时分秒   
select trunc(sysdate) from dual  
显示星期几   
SELECT to\_char(SYSDATE,'Day') FROM dual  
取得某个月的天数   
SELECT trunc(last\_day(SYSDATE))- to\_date(to\_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')+1 FROM dual  
判断是否闰年   
SELECT decode(to\_char(last\_day(to\_date(to\_char(SYSDATE,'yyyy')||'-02-01','yyyy-mm-dd')),'dd'),'28','平年','闰年') FROM dual  
一个季度多少天   
SELECT last\_day(to\_date(to\_char(SYSDATE,'yyyy-')||lpad(floor(to\_number(to\_char(SYSDATE,'mm'))/3)\*3+3,2,'0')||'-01','yyyy-mm-dd')) - to\_date(to\_char(SYSDATE,'yyyy-')||lpad(floor(to\_number(to\_char(SYSDATE,'mm'))/3)\*3+1,2,'0')||'-01','yyyy-mm-dd')+1 FROM dual