Oracle获得两个日期之间的时间差（年、月、天、时、分、秒）

本文链接：https://blog.csdn.net/Denglishang/article/details/81982524

select 
EXTRACT(year FROM to_date('2019-09-24 19:38:45','yyyy-mm-dd hh24:mi:ss'))
   -EXTRACT(year FROM to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24:mi:ss')) as diff_years,-- 年

months_between(to_date('2019-09','yyyy-mm'),to_date('2018-08','yyyy-mm')) as diff_months1,-- 月

(EXTRACT(year FROM to_date('2019-09-24 19:38:45','yyyy-mm-dd hh24:mi:ss'))
  -EXTRACT(year FROM to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24:mi:ss'))) * 12 
  +EXTRACT(month FROM to_date('2019-09-24 19:38:45','yyyy-mm-dd hh24:mi:ss'))
  -EXTRACT(month FROM to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24:mi:ss')) as diff_months2, -- 月
  
ceil((To_date('2018-09-24 19:38:45','yyyy-mm-dd hh24:mi:ss')-to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24;mi:ss'))) as diff_days, -- 天
ceil((To_date('2018-08-23 19:13:01','yyyy-mm-dd hh24-mi-ss')-to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24-mi-ss'))*24) as diff_hours， -- 小时
ceil((To_date('2018-08-23 19:13:01','yyyy-mm-dd hh24-mi-ss')-to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24-mi-ss'))*24*60) as diff_minutes ，-- 分钟
ceil((To_date('2018-08-23 19:13:01','yyyy-mm-dd hh24-mi-ss')-to_date('2018-08-23 10:13:01','yyyy-mm-dd hh24-mi-ss'))*24*60*60) as diff_seconds -- 秒
from dual;