oracle 取当前日期时间的前一天前一小时前一分钟前一秒

参考地址：http://wentao365.iteye.com/blog/779492

SELECT '当前时间' TITLE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') TIME
  FROM DUAL --当前时间
UNION ALL
SELECT '前一天前一小时前一分钟前一秒' TITLE,
       TO_CHAR(SYSDATE - 1 - 1 / 24 - 1 / 24 / 60 - 1 / 24 / 60 / 60,
               'yyyy-mm-dd hh24:mi:ss') TIME
  FROM DUAL
UNION ALL
SELECT '一分钟前' TITLE,
       TO_CHAR(SYSDATE - 1 / 24 / 60, 'yyyy-mm-dd hh24:mi:ss')
  FROM DUAL


某个时间之后571天的时间是：
select to_date('2009-11-16 14:33:27','yyyy-MM-dd HH24:mi:ss') + 571 from dual



引用
sysdate+(5/24/60/60) 在系统时间基础上延迟5秒
sysdate+5/24/60 在系统时间基础上延迟5分钟
sysdate+5/24 在系统时间基础上延迟5小时
sysdate+5 在系统时间基础上延迟5天
add_months(sysdate,-5) 在系统时间基础上延迟5月
add_months(sysdate,-5*12) 在系统时间基础上延迟5年


查询10分钟前的记录
Java代码  收藏代码

    select * from sq_leavemessage m where sysdate-m.createtime<=10/60/24   



1天前创建的产品
Java代码  收藏代码

    select pv.create_time from product_version pv where round(to_number(sysdate-pv.create_time))<=1  



6小时前创建的产品
Java代码  收藏代码

    select pv.create_time from product_version pv where round(to_number(sysdate-pv.create_time)*24)<=6  



1分钟前创建的产品
Java代码  收藏代码

    select pv.create_time from product_version pv where round(to_number(sysdate-pv.create_time)*1440)<=1  

<a target=_blank href="http://blog.csdn.net/z69183787/article/details/22409771" style="text-decoration: none; color: rgb(0, 0, 0); font-family: "microsoft yahei"; font-size: 18px; font-weight: bold;">Oracle sysdate 时间加减</a>

<p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;">加法 </p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,add_months(sysdate,12) from dual;        --加1年</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,add_months(sysdate,1) from dual;        --加1月</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1星期</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1天</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1小时</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1分钟</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1秒</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;">减法</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,add_months(sysdate,-12) from dual;        --减1年</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,add_months(sysdate,-1) from dual;        --减1月</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual;   --减1星期</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual;   --减1天</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1小时</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1分钟</p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"></p><p style="margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px; color: rgb(69, 69, 69); font-family: tahoma, helvetica, arial; font-size: 14px; line-height: 14px;"> select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1秒</p>