Oracle 时间运算

1.加减年月日

with TEMP as (
select to_date('2019-5-3','yyyy-MM-dd hh24:mi:ss') as gmt_time from dual
)
select TO_CHAR(GMT_TIME,'yyyy-MM-dd') as 测试时间,
TO_CHAR(GMT_TIME-5,'yyyy-MM-dd') as 减5天,
TO_CHAR(GMT_TIME+5,'yyyy-MM-dd') as 加5天,
TO_CHAR(ADD_MONTHS(GMT_TIME,-5),'yyyy-MM-dd') as 减5个月,
TO_CHAR(ADD_MONTHS(GMT_TIME,5),'yyyy-MM-dd') as 加5个月,
TO_CHAR(ADD_MONTHS(GMT_TIME,-5*12),'yyyy-MM-dd') as 减5年,
to_char(Add_months(GMT_TIME,5*12),'yyyy-MM-dd') as 加5年
from temp;

结果如下图：

2.加减时分秒

with TEMP as (
select to_date('2019-5-3','yyyy-MM-dd hh24:mi:ss') as gmt_time from dual
)
select TO_CHAR(GMT_TIME,'yyyy-MM-dd hh24:mi:ss') as 测试时间,
TO_CHAR(GMT_TIME-5/24/60/60,'yyyy-MM-dd hh24:mi:ss') as 减5秒,
TO_CHAR(GMT_TIME+5/24/60/60,'yyyy-MM-dd hh24:mi:ss') as 加5秒,
TO_CHAR(GMT_TIME-5/24/60,'yyyy-MM-dd hh24:mi:ss') as 减5分钟,
TO_CHAR(GMT_TIME+5/24/60,'yyyy-MM-dd hh24:mi:ss') as 加5分钟,
TO_CHAR(GMT_TIME-5/24,'yyyy-MM-dd hh24:mi:ss') as 减5小时,
TO_CHAR(GMT_TIME+5/24,'yyyy-MM-dd hh24:mi:ss') as 加5小时
from temp;

结果如下图