ORACEL数据库获取两个时间之前的小时数

一、获取两个时间之前的小时数

select ceil((To_date('2008-05-01 02:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 ) 在厂小时数 FROM SCM_GDHJLD2

二、截取字符串

 select substr(DIAODAOJIAOWANSHIJIAN,1,18) from SCM_GDHJLD2

三、DATE类型转为数字类型

select to_number(to_char(
to_date(to_char(DIAOHUIJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-to_date
(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')))*24 as 在厂时间 from SCM_GDHJLD2

四、比较两个日期大小。

select * from scm_gdhjld2 where to_date(to_char(DIAOHUIJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-21 00:00:00','yyyy-mm-dd hh24:mi:ss')

五、修改在厂时间字段的值（调到时间和调回时间比较）。

update SCM_GDHJLD2 set zaichangshijian = to_char(to_number(to_char(
to_date(to_char(DIAOHUIJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-to_date
(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')))*24)
where to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-21 00:00:00','yyyy-mm-dd hh24:mi:ss')

六、获取系统当前时间。

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

七、修改在厂时间字段的值（调到时间和系统当前时间比较）。

update SCM_GDHJLD2 set zaichangshijian = to_char(to_number(to_char(
to_date((select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual),'yyyy-mm-dd hh24:mi:ss')-to_date
(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')))*24)
where to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-21 00:00:00','yyyy-mm-dd hh24:mi:ss')

八、写的存储过程(写完之后写一个计时器每隔一小时调用这个存储过程，即可完成前台数据的更新)。

CREATE OR REPLACE  PROCEDURE HOURS() AS
Cursor cursor is select diaohuijiaowanshijian from SCM_GDHJLD2 where
to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-24 00:00:00','yyyy-mm-dd hh24:mi:ss');
diaohuijiaowanshijian TIMESTAMP(6);
BEGIN
for diaohuijiaowanshijian in cursor loop
 if(diaohuijiaowanshijian is null)
 then
 update SCM_GDHJLD2 set zaichangshijian = to_char(to_number(to_char(
to_date((select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual),'yyyy-mm-dd hh24:mi:ss')-to_date
(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')))*24)
where to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-21 00:00:00','yyyy-mm-dd hh24:mi:ss');
else then
update SCM_GDHJLD2 set zaichangshijian = to_char(to_number(to_char(
to_date(to_char(DIAOHUIJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-to_date
(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')))*24)
where to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
>to_date('2017-03-24 00:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
end loop;
END HOURS;

 

V_SCM_GDHJLD2    FOR UPDATE

 

九、写一个视图，可以直接实现功能。

  CREATE OR REPLACE FORCE VIEW "ZBWL"."V_SCM_GDHJLD2" ("BILLDTLID", "BILLID", "ROWNO", "CHEXING", "CHEHAO", "FAZHAN", "SHOUHUODANWEI", "SUDU", "MAOZHONG", "PIZHONG", "JINGZHONG", "PINMING", "DIAODAOJIAOWANSHIJIAN", "KAISHIZHUANGXIESHIJIAN", "ZHUANGXIEWANLESHIJIAN", "DIAOHUIJIAOWANSHIJIAN", "DAOZHAN", "BIAOZHONG", "YINGKUI", "CHESHU", "ZAICHANGSHIJIAN") AS  FOR UPDATE
  select  BILLDTLID,BILLID,ROWNO,CHEXING,CHEHAO,FAZHAN,SHOUHUODANWEI,SUDU,MAOZHONG,PIZHONG,JINGZHONG,PINMING,
 DIAODAOJIAOWANSHIJIAN,
 KAISHIZHUANGXIESHIJIAN ,
 ZHUANGXIEWANLESHIJIAN ,
 DIAOHUIJIAOWANSHIJIAN ,
 DAOZHAN,
 BIAOZHONG,
 YINGKUI,
 CHESHU,
ROUND (( case  when DIAOHUIJIAOWANSHIJIAN IS NULL  then ((select sysdate  from dual))- to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
else to_date(to_char(DIAOhuiJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')- to_date(to_char(DIAODAOJIAOWANSHIJIAN,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')  end )*24,1) AS ZAICHANGSHIJIAN  from scm_gdhjld2
;

十、建立的定时器。

DECLARE
  JOB1 PLS_INTEGER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(JOB       => JOB1,
                      WHAT      => 'HOURS;',
                      NEXT_DATE => SYSDATE,
                      INTERVAL  => 'sysdate+1/24');
  COMMIT;
END;

十一、查看定时器。

select   *   from   user_jobs;

十二、查看定时器执行情况。

select job,last_date,last_sec,next_date,next_sec,broken,failures from dba_jobs;