Oracle 获取以小时、分钟和秒为单位的日期时间戳差异。

嗨，团队，

您能否帮助我了解如何找到要以小时，分钟和秒为单位返回的表的2个日期时间戳列之间的差异？

例如:

我确实有一个表a，其中有三列，其中C1为主键，c2为-intime和c3-out time

C1

过程1

C2 / Intime

10/7/2013 7:32:50下午

C3/运行时间

10/7/2013 7:39:52下午


我想看到结果列显示出时间为07:02 (即7分钟和2秒) 或值为bean整数值在秒-7*60 (秒) 2秒 = 422秒。

同样需要使用sql查询来实现

如果日期确实是时间戳，那么这很容易-减去它们，结果是一天到第二个间隔。这些已经是小时: 分钟: 第二种格式!

with rws as (
  select timestamp'2017-03-01 01:00:00' t1, 
         timestamp'2017-03-01 02:34:56' t2 
  from dual
)
  select t2-t1  diff_interval
  from rws;

DIFF_INTERVAL        
+00 01:34:56.000000  

或者，您可以使用extract() 手动获取组件:

with rws as (
  select timestamp'2017-03-01 01:00:00' t1, 
         timestamp'2017-03-01 02:34:56' t2 
  from dual
)
  select extract(hour from (t2-t1)) hrs, 
         extract(minute from (t2-t1)) mins,  
         extract(second from (t2-t1)) secs,
         t2-t1  diff_interval
  from rws;

HRS  MINS  SECS  DIFF_INTERVAL        
1    34    56    +00 01:34:56.000000

如果值是日期，则减去它们会产生一个数字。提取仅适用于日期时间或时间间隔。所以你会得到一个错误:

with rws as (
  select date'2017-03-01' t1, 
         date'2017-03-01'+1/15.1 t2 
  from dual
)
  select extract(hour from (t2-t1)) hrs, 
         extract(minute from (t2-t1)) mins,  
         extract(second from (t2-t1)) secs,
         t2-t1 diff_interval
  from rws;

SQL Error: ORA-30076: invalid extract field for extract source

但是你可以通过将日期转换为时间戳来解决这个问题:

with rws as (
  select date'2017-03-01' t1, 
         date'2017-03-01'+1/15.1 t2 
  from dual
), tstamps as (
  select to_timestamp(to_char(t1, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') t1,
         to_timestamp(to_char(t2, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') t2
  from rws
)
  select extract(hour from (t2-t1)) hrs, 
         extract(minute from (t2-t1)) mins,  
         extract(second from (t2-t1)) secs,
         t2-t1 diff_interval
  from   tstamps;

HRS  MINS  SECS  DIFF_INTERVAL        
1    35    22    +00 01:35:22.000000