参考地址: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>