Oracle 减去2个时间戳以找到小时的差异

select 
extract(HOUR FROM diff) from (
select '2/14/2017 3:39:15.097484 PM +00:00' - '2/14/2017 1:39:15.097484 PM +00:00' as diff from dual);

[Error] Execution (49: 19): ORA-30076: invalid extract field for extract source

复制

select extract(hour from diff) hrs from (
select 
  to_timestamp_tz('2/14/2017 3:39:15.097484 PM +00:00', 'mm/dd/yyyy hh:mi:ssxff PM tzh:tzm') -
  to_timestamp_tz('2/14/2017 1:39:15.097484 PM +00:00', 'mm/dd/yyyy hh:mi:ssxff PM tzh:tzm') diff
from dual
);

HRS  
2    
复制